<?php


namespace App\Services;


use App\Models\AuthenticLog;
use App\Models\MemberOther;
use App\Models\NewAdmin;
use App\Models\Order;
use App\Models\OrderGoods;
use App\Models\OrderLease;
use App\Models\OrderRadarXinyan;
use App\Models\OrderRefund;
use App\Models\OrderRemark;
use App\Models\OtherWithholdLog;
use App\Models\ShopTrans;
use App\Models\ShopUser;
use App\Models\ShopWithdrawal;
use App\Models\SmsRecord;
use App\Models\SmsTemplate;
use App\Models\Source;
use App\Models\SaleUser;
use App\Models\TransDetail;
use Illuminate\Support\Facades\DB;
use PHPExcel\Create;
use App\Models\TwoElements;

class ExportService
{
    //文件名称
    const FILE_ORDER_LIST = '订单列表';
    const extend_data_export = '推广统计导出';
    const RISK_DATA_EXPORT = '风控统计导出';
   const  extend_data_export_detail='推广统计详情导出';
    const FILE_ORDER_DELIVER_LIST = '订单发货格式列表';
    const FILE_ORDER_SALE_LIST = '推广订单列表';
    const FILE_ORDER_REMIND_LIST = '划扣订单列表';
    const FILE_ORDER_REMIND_BRIEF_LIST = '还款提醒订单列表';
    const FILE_ORDER_FINANCE_LIST = '订单财务列表';
    const FILE_LEASE_FINANCE_LIST = '账单财务列表';
    const FILE_ORDER_LEASE_LIST = '订单分期明细列表';
    const FILE_OVERDUE_FINANCE_LIST = '逾期列表';
    const FILE_RECORD_FINANCE_LIST = '对账流水';
    const FILE_ORDER_SETTLE_LIST = '账单结算列表';
    const FILE_HUABEI_ORDER_SETTLE_LIST = '花呗账单结算列表';
    const FILE_WITHDRAWAL_LOG_LIST = '提现记录';
    const FILE_HUABEI_ORDER_FINANCE_LIST = '财务花呗账单列表';
    const FILE_COLLECTION_ORDER_FINANCE_LIST = '财务回款列表';
    const FILE_EQUIPMENT_ORDER_FINANCE_LIST = '设备分期列表';
    const FILE_WITHDRAWAL_CHECK_LIST = '提现审核列表';

    const FILE_HUABEI_ORDER_LIST = '花呗订单列表';
    const FILE_BUYOUT_ORDER_LIST = '买断订单列表';
    const FILE_SIGNED_ORDER_LIST = '签约待付款订单列表';

    const FILE_REPORT_BILL_LIST = '融资报单列表';
    //筛选条件
    const   CONDITION_LIST = [
        self::FILE_ORDER_LIST=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#用户名：',
            'receivingMobile'=>'筛选条件#手机号：',
            'riskName'=>'筛选条件#风控姓名：',
            'riskMobile'=>'筛选条件#风控手机号：',
            'shopName'=>'筛选条件#供应商：',
            'orderCreateTime'=>'筛选条件#创建时间：',
        ],
        self::extend_data_export=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#用户名：',
            'receivingMobile'=>'筛选条件#手机号：',
            'riskName'=>'筛选条件#风控姓名：',
            'riskMobile'=>'筛选条件#风控手机号：',
            'shopName'=>'筛选条件#供应商：',
        ],
        self::extend_data_export_detail=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#用户名：',
            'receivingMobile'=>'筛选条件#手机号：',
            'riskName'=>'筛选条件#风控姓名：',
            'riskMobile'=>'筛选条件#风控手机号：',
            'shopName'=>'筛选条件#供应商：',
        ],
        self::FILE_ORDER_LEASE_LIST=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#用户名：',
            'receivingMobile'=>'筛选条件#手机号：',
            'riskName'=>'筛选条件#风控姓名：',
            'riskMobile'=>'筛选条件#风控手机号：',
            'shopName'=>'筛选条件#供应商：',
            'orderCreateTime'=>'筛选条件#创建时间：',
        ],
        self::FILE_ORDER_SALE_LIST=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'sale_name'=>'筛选条件#推广员：',
            'createTime'=>'筛选条件#创建时间：',
        ],
        self::FILE_ORDER_REMIND_BRIEF_LIST => [
            'orderStatus'=>'筛选条件#订单状态：',
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#用户名：',
            'receivingMobile'=>'筛选条件#手机号：',
            'riskName'=>'筛选条件#风控姓名：',
            'riskMobile'=>'筛选条件#风控手机号：',
            'shopName'=>'筛选条件#供应商：',
            'orderCreateTime'=>'筛选条件#创建时间：',
        ],
        self::FILE_ORDER_DELIVER_LIST=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#用户名：',
            'receivingMobile'=>'筛选条件#手机号：',
            'riskName'=>'筛选条件#风控姓名：',
            'riskMobile'=>'筛选条件#风控手机号：',
            'shopName'=>'筛选条件#供应商：',
            'orderCreateTime'=>'筛选条件#创建时间：',
        ],
        self::FILE_ORDER_REMIND_LIST=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#用户名：',
            'receivingMobile'=>'筛选条件#手机号：',
            'riskName'=>'筛选条件#风控姓名：',
            'riskMobile'=>'筛选条件#风控手机号：',
            'shopName'=>'筛选条件#供应商：',
            'orderCreateTime'=>'筛选条件#创建时间：',
        ],
        self::FILE_ORDER_FINANCE_LIST=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#用户名：',
            'shopName'=>'筛选条件#供应商：',
            'orderCreateTime'=>'筛选条件#下单日：',
            'leaseTerm'=>'筛选条件#总期数：',
            'orderPayTime'=>'筛选条件#实际支付日：',
            'leaseTime'=>'筛选条件#预计扣款日：',
        ],
        self::FILE_LEASE_FINANCE_LIST=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#下单姓名：',
            'receivingMobile'=>'筛选条件#下单电话：',
            'shopName'=>'筛选条件#供应商：',
            'orderCreateTime'=>'筛选条件#下单日：',
            'leaseTerm'=>'筛选条件#第几期：',
            'orderPayTime'=>'筛选条件#实际支付日：',
            'leaseTime'=>'筛选条件#预计扣款日：',
            'refundTime'=>'筛选条件#退款时间：',
        ],
        self::FILE_OVERDUE_FINANCE_LIST=>[
            'orderNo'=>'筛选条件#订单号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#姓名：',
            'receivingMobile'=>'筛选条件#手机号：',
            'shopName'=>'筛选条件#供应商：',
            'sendStatus'=>'筛选条件#短信状态：',
            'leaseTime'=>'筛选条件#预计扣款日：',
            'overdueDay'=>'筛选条件#逾期天数：',
        ],
        self::FILE_RECORD_FINANCE_LIST=>[
            'orderNo'=>'筛选条件#订单号：',
            'shopName'=>'筛选条件#供应商：',
            'moneyFlow'=>'筛选条件#资金流向：',
            'flowType'=>'筛选条件#类型：',
            'createTime'=>'筛选条件#入账时间：',
        ],
        self::FILE_ORDER_SETTLE_LIST=>[
            'order_no'=>'筛选条件#订单号：',
            'user_name'=>'筛选条件#用户名称：',
            'shop_name'=>'筛选条件#供应商名称：',
            'lease_term'=>'筛选条件#租期筛选：',
            'pay_time'=>'筛选条件#实际支付日：',
            'start_time'=>'筛选条件#起租日筛选：',
        ],
        self::FILE_HUABEI_ORDER_SETTLE_LIST=>[
            'order_no'=>'筛选条件#订单号：',
            'user_name'=>'筛选条件#用户名称：',
            'shop_name'=>'筛选条件#供应商名称：',
            'lease_term'=>'筛选条件#租期筛选：',
            'pay_time'=>'筛选条件#实际支付日：',
            'start_time'=>'筛选条件#起租日筛选：',
        ],
        self::FILE_WITHDRAWAL_LOG_LIST=>[
            'adminMobile'=>'筛选条件#所属账号：',
            'shopName'=>'筛选条件#供应商名称：',
            'status'=>'筛选条件#状态：',
            'arrivalTime'=>'筛选条件#申请时间：',
        ],
        self::FILE_HUABEI_ORDER_FINANCE_LIST=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'shopName'=>'筛选条件#供应商名称：',
            'orderNo'=>'筛选条件#订单编号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#用户姓名：',
            'orderCreateTime'=>'筛选条件#订单创建时间：',
            'orderPayTime'=>'筛选条件#实际支付时间：',
            'leaseTerm'=>'筛选条件#总租期：',
        ],
        self::FILE_COLLECTION_ORDER_FINANCE_LIST=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'shopName'=>'筛选条件#供应商名称：',
            'orderNo'=>'筛选条件#订单编号：',
            'goodsName'=>'筛选条件#商品名称：',
            'receivingName'=>'筛选条件#下单姓名：',
            'receivingMobile'=>'筛选条件#下单电话：',
            'orderCreateTime'=>'筛选条件#订单创建时间：',
            'leaseTime'=>'筛选条件#预计扣款时间：',
            'orderPayTime'=>'筛选条件#实际支付时间：',
            'leaseTerm'=>'筛选条件#总租期：',
            'refundTime'=>'筛选条件#退款时间：',
        ],
        self::FILE_EQUIPMENT_ORDER_FINANCE_LIST=>[
            'orderStatus'=>'筛选条件#订单状态：',
            'shopName'=>'筛选条件#供应商名称：',
            'orderNo'=>'筛选条件#订单编号：',
            'goodsName'=>'筛选条件#商品名称：',
            'orderCreateTime'=>'筛选条件#订单创建时间：',
        ],
        self::FILE_REPORT_BILL_LIST=>[
            'trade_no'=>'筛选条件#订单号：',
            'goods_title'=>'筛选条件#商品名称：',
            'realname'=>'筛选条件#下单姓名：',
            'mobile'=>'筛选条件#下单电话：',
            'create_time'=>'筛选条件#创建时间：',
            'status'=>'筛选条件#订单状态：',
            'sync_status'=>'筛选条件#同步状态：',
            'shopname'=>'筛选条件#供应商：',
        ],
        self::FILE_WITHDRAWAL_CHECK_LIST=>[
            'payee_name'=>'筛选条件#收款人姓名：',
            'payee_account'=>'筛选条件#支付宝账号：',
            'applyTime'=>'筛选条件#申请时间：',
            'arrivalTime'=>'筛选条件#入账时间：',
            'shopname'=>'筛选条件#供应商：',
        ],
    ];
    //表头信息
    const FILE_HEADER = [
        //下单日期	支付日期	平台	姓名	订单号	型号	新机/二手
        //审核人	总回款	拿货总价	期数	用户付款金额	用户付款期数	是否有锁	意外保障金
        //	流量费	运营成本	实际支付成本	是否买断	剩余应还款	一期回款（不含保险）
        //第二期	应回1	回款1	第三期	应回2	回款2	第四期	应回3	回款3
        //	第五期	应回4	回款4	第六期	应回5	回款5	第七期	应回6
        //回款6	第八期	应回7	回款7	第九期	应回8	回款8	第十期	应回9	回款9
        //第十一期	应回10	回款10	第十二期	应回11	回款11	买断/卖	总回款

        self::FILE_ORDER_LEASE_LIST => [
            "下单日期",
            "支付日期",
            "平台",
            "姓名",
            "订单号",
            "型号",
            "新机/二手",
            "审核人",
            "总回款",
            "拿货总价",
            "期数",
            "用户付款金额",
            "用户付款期数",
            "是否有锁","意外保障金","流量费","运营成本","实际支付成本","租赁方式","剩余应还款","一期回款（不含保险）","第二期","应回1","回款1",
            "第三期","应回2","回款2","第四期","应回3","回款3","第五期","应回4","回款4","第六期","应回5","回款5","第七期","应回6","回款6","第八期",
            "应回7","回款7","第九期","应回8","回款8","第十期","应回9","回款9","第十一期","应回10","回款10","第十二期","应回11","回款11","买断/卖","总回款"
        ],
        self::FILE_ORDER_SALE_LIST => [
            "订单号",
            "姓名",
            "手机号码",
            "商品名称",
            "推广员",
            "租期",
            "租赁时间",
            "租金",
            "订单状态",
            "佣金",
        ],
        self::extend_data_export => [
            "推广账号",
            "渠道名称",
            "pv",
            "uv",
            "注册用户",
            "注册率(UV/注册用户)",
            "总订单量",
            "待审核",
            "待付款",
            "待发货",
            "已发货",
            "租用中",
            "交易完成",
            "取消订单",
            "还款中订单",
            "逾期订单",
            "退款订单",
        ],
        self::extend_data_export_detail => [
            "日期",
            "pv",
            "uv",
            "注册用户",
            "注册率(UV/注册用户)",
            "总订单量",
            "待审核",
            "待付款",
            "待发货",
            "已发货",
            "租用中",
            "交易完成",
            "取消订单",
            "还款中订单",
            "逾期订单",
            "退款订单",
        ],
        self::RISK_DATA_EXPORT => [
            "请求编号",
            "用户名",
            "用户身份证号",
            "用户手机号",
            "查询方名称",
            "状态",
            "结果",
            "风险等级",
            "来源",
            "是否授权免押",
            "查询时间",
        ],

        self::FILE_ORDER_LIST => [
            "订单号",
            "创建时间",
            "商品名称",
            "商品数量",
            "订单来源",
            "是否有锁",
            "拿货价",
            "总租金",
            "期数",
            "每期租金",
            "实际付款金额",
            "实际付款期数",
            "租期	",
            "免押类型",
            "免押金额",
            "实付押金",
            "支付方式",
            "订单状态",
            "风控姓名",
            "性别",
            "身份证号码",
            "年龄",
            "风控手机号",
            "用户名",
            "手机号",
            "省",
            "市",
            "区",
            "下单地址",
            "用户备注",
            "客服备注",
            "逾期天数",
            "逾期金额",
            "买断金额",
            "退款金额",
            "续租关系",
            "供应商名称",
            "逾期费",
            "逾期总天数",
            "紧急联系人及备注",
            "设备型号"
        ],
        self::FILE_ORDER_DELIVER_LIST => [
            "订单号",
            "订单状态",
            "姓名",
            "手机号码",
            "机型",
            "审核人",
            "拿货价",
            "下单日期",
            "支付日期",
            "是否有锁",
            "来源",
            "是否碎屏保",
            "期数",
            "预付期数",
            "实际付款期数",
            "已付款",
            "实际付款",
            "总金额",
            "实际总金额",
            "实际成本",
            "剩余待还款",
            "是否买断",
            "可买断金",
            "流量来源",
        ],
        self::FILE_ORDER_REMIND_LIST => [
            "订单号",
            "创建时间",
            "商品名称",
            "商品数量",
            "订单来源",
            "总租金",
            "期数",
            "实际付款金额",
            "实际付款期数",
            "租期	",
            "免押类型",
            "免押金额",
            "实付押金",
            "支付方式",
            "订单状态",
            "风控姓名",
            "风控手机号",
            "用户名",
            "手机号",
            "省",
            "市",
            "区",
            "下单地址",
            "用户备注",
            "客服备注",
            "逾期天数",
            "买断金额",
            "退款金额",
            "续租关系",
            "供应商名称",
        ],
        self::FILE_ORDER_REMIND_BRIEF_LIST => [
            "供应商",
            "姓名",
            "电话",
            "还款金额",
            "是否还款",
            "备注",
            "人员",
            "是否有锁",
            "部分还款",
            "还款期数",
        ],
        self::FILE_ORDER_FINANCE_LIST => [
            "租期ID",
            "供应商",
            "客户姓名",
            "商品名称",
            "商品数量",
            "期数",
            "账单总金额",
            "账单金额",
            "实付金额",
            "订单退款金额",
            "结算租金",
            "结算手续费",
            "结算综合费用",
            "结算时间",
            "租期",
            "预计扣款日",
            "实际支付日",
            "扣款次数",
            "收货人",
            "身份证",
            "电话",
            "地址",
            "商户类型",
            "订单编号",
            "订单来源",
            "支付方式",
            "支付流水号",
            "退款流水号",
            "支付状态",
            "订单状态",
            "商户号",
            "备注",
        ],
        self::FILE_LEASE_FINANCE_LIST => [
            "租期ID",
            "订单号",
            "商品名称",
            "商品数量",
            "订单来源",
            "第几期",
            "租期",
            "支付方式",
            "下单日",
            "预计扣款日",
            "实际支付日",
            "账单金额",
            "实付金额",
            "退款金额",
            "结算租金",
            "结算时间",
            "扣款次数",
            "支付状态",
            "订单状态",
            "退款时间",
            "真实姓名",
            "支付宝手机号",
            "收货人姓名",
            "收货人手机号",
            "省",
            "市",
            "区",
            "下单地址",
            "备注",
            "供应商名称"
        ],
        self::FILE_OVERDUE_FINANCE_LIST => [
            "订单号",
            "商品名称",
            "数量",
            "第几期",
            "租期",
            "预计扣款日",
            "账单金额",
            "实付金额",
            "扣款次数",
            "姓名",
            "手机号",
            "订单状态",
            "支付状态",
            "供应商名称",
            "发送次数",
            "收货人",
            "省",
            "市",
            "区",
            "收货地址",
            "逾期天数",
            "短信状态"
        ],
        self::FILE_RECORD_FINANCE_LIST => [
            "交易ID",
            "供应商",
            "账户类型",
            "订单号",
            "租赁方式",
            "总金额",
            "第几期",
            "租金",
            "实付金额",
            "支付方式",
            "手续费率",
            "手续费",
            "综合费用",
            "实名认证费用明细",
            "合同费用明细",
            "风控费用明细",
            "手续费金额",
            "结算金额",
            "交易金额",
            "交易前余额",
            "交易后余额",
            "来源或去路",
            "交易说明",
            "订单号",
            "风控姓名",
            "收货人姓名",
            "创建时间",
            "状态"
        ],
        self::FILE_ORDER_SETTLE_LIST => [
            "ID",
            "订单号",
            "租赁方式",
            "总金额",
            "期数",
            "租期",
            "支付方式",
            "预计扣款日",
            "实际扣款日",
            "账单金额",
            "实付金额",
            "支付状态",
            "逾期天数",
            "结算租金",
            "手续费率",
            "手续费",
            "结算时间",
            "结算原因",
            "用户姓名",
            "用户手机号",
            "用户收货地址",
            "账户类型",
            "订单状态",
            "结算状态",
            "供应商名称"
        ],
        self::FILE_HUABEI_ORDER_SETTLE_LIST => [
            "ID",
            "订单号",
            "期数",
            "租期",
            "支付方式",
            "实际扣款日",
            "账单金额",
            "实付金额",
            "花呗手续费承担",
            "结算租金",
            "手续费",
            "结算时间",
            "支付状态",
            "用户姓名",
            "用户手机号",
            "用户收货地址",
            "账户类型",
            "订单状态",
            "结算状态",
            "供应商名称"
        ],
        self::FILE_WITHDRAWAL_LOG_LIST => [
            "ID",
            "供应商",
            "所属账号",
            "提现金额",
            "提现方式",
            "收款人信息",
            "流水号",
            "申请时间",
            "审核时间",
            "到账时间",
            "状态",
            "拒绝原因",
            "操作人"
        ],
        self::FILE_HUABEI_ORDER_FINANCE_LIST => [
            "ID",
            "订单号",
            "商品名称",
            "商品数量",
            "期数",
            "租期",
            "支付方式",
            "下单日期",
            "实际支付日",
            "账单金额",
            "实付金额",
            "花呗手续费承担",
            "手续费",
            "结算租金",
            "结算时间",
            "结算状态",
            "订单状态",
            "实名认证姓名",
            "用户姓名",
            "用户手机号",
            "用户地址",
            "供应商名称",
        ],
        self::FILE_COLLECTION_ORDER_FINANCE_LIST => [
            "ID",
            "订单号",
            "商品名称",
            "姓名",
            "电话",
            "下单日期",
            "总租期",
            "月租金",
            "回款日(1)",
            "回款金额(1)",
            "结算日(1)",
            "结算金额(1)",
            "回款日(2)",
            "回款金额(2)",
            "结算日(2)",
            "结算金额(2)",
            "回款日(3)",
            "回款金额(3)",
            "结算日(3)",
            "结算金额(3)",
            "回款日(4)",
            "回款金额(4)",
            "结算日(4)",
            "结算金额(4)",
            "回款日(5)",
            "回款金额(5)",
            "结算日(5)",
            "结算金额(5)",
            "回款日(6)",
            "回款金额(6)",
            "结算日(6)",
            "结算金额(6)",
            "回款日(7)",
            "回款金额(7)",
            "结算日(7)",
            "结算金额(7)",
            "回款日(8)",
            "回款金额(8)",
            "结算日(8)",
            "结算金额(8)",
            "回款日(9)",
            "回款金额(9)",
            "结算日(9)",
            "结算金额(9)",
            "回款日(10)",
            "回款金额(10)",
            "结算日(10)",
            "结算金额(10)",
            "回款日(11)",
            "回款金额(11)",
            "结算日(11)",
            "结算金额(11)",
            "回款日(12)",
            "回款金额(12)",
            "结算日(12)",
            "结算金额(12)",
            "累计应收",
            "累计应付",
            "欠收金额",
            "供应商名称",
        ],
        self::FILE_EQUIPMENT_ORDER_FINANCE_LIST => [
            "ID",
            "订单号",
            "商品名称",
            "总租期",
            "月租金/台",
            "租金/台",
            "分期台数",
            "总租金",
            "下单日",
            "起租日",
            "到期日",
            "订单状态",
            "当月应收期数",
            "当月应收金额",
            "当月实收金额",
            "当月逾期金额",
            "累计应收期数",
            "累计应收金额",
            "累计实收金额",
            "累计逾期金额",
            "应收账款",
            "违约金额",
            "供应商名称",
        ],
        self::FILE_REPORT_BILL_LIST => [
            "ID",
            "订单号",
            "创建时间",
            "商品名称",
            "套餐名称",
            "数量",
            "总租期",
            "总租金",
            "下单姓名",
            "下单电话",
            "地址区域",
            "详细地址",
            "订单状态",
            "同步状态",
            "供应商",
        ],
        self::FILE_WITHDRAWAL_CHECK_LIST => [
            "ID",
            "账户类型",
            "供应商名称",
            "提现方式",
            "提现金额",
            "支付宝账号",
            "收款人姓名",
            "申请时间",
            "入账时间",
            "提现状态",
            "原因",
            "审核时间",
            "操作人"
        ],
    ];

    /**
     * 条件筛选
     * @param $params
     * @param string $condition
     * @return \string[][]
     */
    public static function conditionFind($params,$type){
        $data = [["导出时间#".dateFormat(time())]];
        $condition = self::CONDITION_LIST[$type];
        foreach ($params as $key=>$item){
            if(isset($condition[$key]) && $item){
                $data[] = [$condition[$key].$item];
            }
        }
        return $data;
    }

    /**
     * 导出文件
     * @param $param
     * @param $data
     * @param $fileName
     * @param string $aliasName
     */
    public static function exportFile($param,$data,$fileName,$aliasName=''){
        set_time_limit(0);
        ini_set('memory_limit', '-1');
        $condition = self::conditionFind($param,$fileName);
        $fileHeader = self::FILE_HEADER[$fileName];
        $name = $aliasName ? $aliasName : $fileName;
        $obj = new Create();
        $obj->exportExcel($condition,$fileHeader,$data,$name.date("Y-m-d-His"));
    }

    /**
     * 导出发货格式文件
     * @param $param
     * @param $data
     * @param $fileName
     * @param string $aliasName
     */
    public static function exportDeliverFile($param,$data,$fileName,$aliasName=''){
        set_time_limit(0);
        ini_set('memory_limit', '-1');
        $condition = self::conditionFind($param,$fileName);
        $fileHeader = self::FILE_HEADER[$fileName];
        $name = $aliasName ? $aliasName : $fileName;
        $obj = new Create();
        $obj->exportExcel($condition,$fileHeader,$data,$name.date("Y-m-d-His"));
    }
    /**
     * 管理后台财务订单列表导出
     * @param $request
     * @return array|false|string
     */
    public static function adminFinanceOrderListExport($request,$shopId=''){
        $orderStatus = $request->input('orderStatus');
        $orderNo = $request->input('orderNo','');
        $goodsName = $request->input('goodsName','');
        $receivingName = $request->input('receivingName','');
        $shopName = $request->input('shopName','');
        $orderCreateTime = $request->input('orderCreateTime','');
        $leaseTime = $request->input('leaseTime','');
        $orderPayTime = $request->input('orderPayTime','');
        $leaseTerm = $request->input('leaseTerm','');
        $where = [];
        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        if($orderStatus){
            if($orderStatus > OrderService::ORDER_STATUS_NOT_PAY){
                $where[] = ['o.status',$orderStatus];
            }else{
                return [];
            }
        }else{
            $where[] = ['o.status','>',OrderService::ORDER_STATUS_NOT_PAY];
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($goodsName){
            $where[] = ['og.goods_title','like','%'.$goodsName.'%'];
        }
        if($receivingName){
            $where[] = ['o.realname','like','%'.$receivingName.'%'];
        }
        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }
        if($leaseTerm){
            $where[] = ['o.month_total',$leaseTerm];
        }
        if($orderCreateTime){
            $orderCreateTime = explode('~', $orderCreateTime);
            $where[] = ['o.create_time','>=',strtotime($orderCreateTime[0])];
            $where[] = ['o.create_time','<=',strtotime($orderCreateTime[1])];
        }
        if($leaseTime){
            $leaseTime = explode('~', $leaseTime);
            $where[] = ['ol.begin_time','>=',strtotime($leaseTime[0])];
            $where[] = ['ol.begin_time','<=',strtotime($leaseTime[1])];
        }
        if($orderPayTime){
            $orderPayTime = explode('~', $orderPayTime);
            $where[] = ['ol.pay_time','>=',strtotime($orderPayTime[0])];
            $where[] = ['ol.pay_time','<=',strtotime($orderPayTime[1])];
        }
        $list = Order::from('sp_order_2 as o')
            ->select(
                'o.id as order_id','o.trade_no','o.rent_total','o.month_total','o.status','o.realname','o.pay_type','o.seller_id',
                'o.mobile','o.address','o.create_time','o.order_from','o.prov','o.city','o.area','o.amount','o.flow_id'
                ,'og.goods_title'
                ,'s.shopname','s.account'
            )
            ->leftjoin('sp_order_lease_2 as ol','o.id','ol.order_id')
            ->leftjoin('sp_order_goods_2 as og','o.id','og.order_id')
            ->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id')
            ->where($where)
            ->orderBy('o.id','desc')
            ->groupBy('o.id')
            ->get();
        $list = obToArray($list);
        $data = [];
        foreach ($list as $k=>$item){
            //订单来源
            $source = Source::where(['source_code'=>$item['order_from'],'status'=>Source::SOURCE_STATUS_ENABLE])->value('source_name');
            $authenticLog = obToArray(AuthenticLog::where(['order_id'=>$item['order_id']])->select('cert_no','cert_name')->first());
            $authenticLog = obToArray($authenticLog);
            $orderPayType = isset(Order::ORDER_PAY_TYPE[$item['pay_type']]) ? Order::ORDER_PAY_TYPE[$item['pay_type']] : '';
            if($item['pay_type'] == Order::ORDER_PAY_TYPE_ZM){
                if(OrderLease::where([['order_id',$item['order_id']],['pay_type',OrderLease::LEASE_PAY_TYPE_ANTCHAIN]])->count() > 0){
                    $orderPayType = '智能合同代扣转预授权';
                }
            }
            //客服备注
            $orderRemark = OrderRemark::getRemark($item['order_id']);
            if($orderRemark){
                $orderRemark = $orderRemark['create_time'].$orderRemark['username'].$orderRemark['remark'];
            }else{
                $orderRemark = '';
            }
            $lease = OrderLease::where(['order_id'=>$item['order_id']])->select(
                'id','which_period','rent','paid_rent','begin_time'
                ,'end_time','status','pay_time','pay_type','settle_amount'
                ,'settle_type','settle_time','reason','withhold_status','overdue_days','trade_no'
            )->get()->toArray();
            foreach ($lease as $v){
                $v['begin_time'] = dateDFormat($v['begin_time']);
                $v['end_time'] = dateDFormat($v['end_time']);
                $v['pay_time'] = dateFormat($v['pay_time']);
                $withhold_num = OtherWithholdLog::where(['lease_id'=>$v['id']])->count();
                $trans_amount = 0;
                $trans_trade_no = '';
                $platform_amount = 0;
                $total_fee = 0;
                if($v['status'] == OrderLease::ORDER_LEASE_STATUS_REFUNDED){
                    $trans = ShopTrans::where(['order_lease_id'=>$v['id'],'trans_type'=>ShopTrans::ORDER_TRANS_TYPE_REFUND])->select('amount','trade_no')->first();
                    $trans = obToArray($trans);
                    if($trans){
                        $trans_amount = $trans['amount'];
                        $trans_trade_no = $trans['trade_no'];
                    }
                }
                $settle_data = '';
                if(in_array($v['settle_type'],[OrderLease::ORDER_LEASE_SETTLE_STATUS_YES,OrderLease::ORDER_LEASE_SETTLE_STATUS_REFUND])){
                    $trans_detail = ShopTrans::where(['order_lease_id'=>$v['id']])->select('service_fee','order_fixed_cost','member_id')->first();
                    $trans_detail = obToArray($trans_detail);
                    if($trans_detail){
                        $platform_amount = $trans_detail['service_fee'];
                        $total_fee = $trans_detail['order_fixed_cost'];
                        if($trans_detail['member_id'] != $item['seller_id']){
                            $settle_data = '(迁移前结算)';
                        }
                    }
                }
                $leasePayType = isset(OrderLease::ORDER_LEASE_PAY_TYPE_LIST[$v['pay_type']]) ? OrderLease::ORDER_LEASE_PAY_TYPE_LIST[$v['pay_type']] : '';

                $data[] = [
                    'lease_id' => ' '.$v['id'],
                    'shopname' => $item['shopname'],
                    'cert_name' => isset($authenticLog['cert_name']) ? $authenticLog['cert_name'] : '',
                    'goods_title' => $item['goods_title'],
                    'amount' => $item['amount'],
                    'total_lease' => $v['which_period'].'/'.$item['month_total'].'期'.$settle_data,
                    'rent_total' => $item['rent_total'],
                    'rent' => $v['rent'],
                    'paid_rent' => $v['paid_rent'],
                    'refund' => $trans_amount,
                    'settle_amount' => $v['settle_amount'],
                    'settle_fee' => $platform_amount,
                    'settle_fee_amount' => $total_fee,
                    'settle_time' => $v['settle_time'],
                    'lease_team' => $v['begin_time'].'至'.$v['end_time'],
                    'begin_time' => $v['begin_time'],
                    'pay_time' => $v['pay_time'],
                    'withhold_num' => $withhold_num,
                    'realname' => $item['realname'],
                    'cert_no' => isset($authenticLog['cert_no']) ? ' '.$authenticLog['cert_no'] : '',
                    'mobile' => ' '.$item['mobile'],
                    'address' => $item['address'],
                    'account' => isset(ShopUser::ACCOUNT_TYPE_LIST[$item['account']]) ? ShopUser::ACCOUNT_TYPE_LIST[$item['account']] : '',
                    'trade_no' => ' '.$item['trade_no'],
                    'source' => $source,
                    'pay_type' => $orderPayType.'/'.$leasePayType,
                    'pay_trade_no' => $v['trade_no'],
                    'refund_no' => $trans_trade_no,
                    'lease_status' => isset(OrderLease::ORDER_LEASE_STATUS_LIST[$v['status']]) ? OrderLease::ORDER_LEASE_STATUS_LIST[$v['status']] : '',
                    'status' => isset(OrderService::ORDER_STATUS_NAME_LIST[$item['status']]) ? OrderService::ORDER_STATUS_NAME_LIST[$item['status']] : '',
                    'shop_no' => $item['flow_id'],
                    'remark' => $orderRemark,
                ];
            }
        }
        return $data;
    }

    /**
     * 管理后台财务账单列表导出
     * @param $request
     * @return array|false|string
     */
    public static function adminFinanceLeaseListExport($request,$shopId=''){
        $orderStatus = $request->input('orderStatus');
        $orderNo = $request->input('orderNo','');
        $goodsName = $request->input('goodsName','');
        $receivingName = $request->input('receivingName','');
        $receivingMobile = $request->input('receivingMobile','');
        $shopName = $request->input('shopName','');
        $orderCreateTime = $request->input('orderCreateTime','');
        $leaseTime = $request->input('leaseTime','');
        $orderPayTime = $request->input('orderPayTime','');
        $leaseTerm = $request->input('leaseTerm','');
        $refundTime = $request->input('refundTime','');
        $where = [];
        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        if($orderStatus){
            if($orderStatus > OrderService::ORDER_STATUS_NOT_PAY){
                $where[] = ['o.status',$orderStatus];
            }else{
                return [];
            }
        }else{
            $where[] = ['o.status','>',OrderService::ORDER_STATUS_NOT_PAY];
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($goodsName){
            $where[] = ['og.goods_title','like','%'.$goodsName.'%'];
        }
        if($receivingName){
            $where[] = ['o.realname','like','%'.$receivingName.'%'];
        }
        if($receivingMobile){
            $where[] = ['o.mobile','like','%'.$receivingMobile.'%'];
        }
        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }
        if($leaseTerm){
            $where[] = ['o.month_total',$leaseTerm];
        }
        if($orderCreateTime){
            $orderCreateTime = explode('~', $orderCreateTime);
            $where[] = ['o.create_time','>=',strtotime($orderCreateTime[0])];
            $where[] = ['o.create_time','<=',strtotime($orderCreateTime[1])];
        }
        if($leaseTime){
            $leaseTime = explode('~', $leaseTime);
            $where[] = ['ol.begin_time','>=',strtotime($leaseTime[0])];
            $where[] = ['ol.begin_time','<=',strtotime($leaseTime[1])];
        }
        if($orderPayTime){
            $orderPayTime = explode('~', $orderPayTime);
            $where[] = ['ol.pay_time','>=',strtotime($orderPayTime[0])];
            $where[] = ['ol.pay_time','<=',strtotime($orderPayTime[1])];
        }
        if($refundTime){
            $refundTime = explode('~', $refundTime);
            $where[] = ['ol.refund_time','>=',$refundTime[0]];
            $where[] = ['ol.refund_time','<=',$refundTime[1]];
        }

        $handel = Order::from('sp_order_2 as o');
        $handel->leftjoin('sp_order_lease_2 as ol','o.id','ol.order_id');
        $handel->leftjoin('sp_order_goods_2 as og','o.id','og.order_id');
        $handel->leftjoin('sp_member_2 as m','m.id','o.uid');
        $handel->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id');
        $handel->select(
            'o.id as order_id','o.trade_no','o.rent_total','o.month_total','o.status','o.amount'
            ,'o.prov','o.city','o.area','o.pay_type as order_pay_type'
            ,'o.realname','o.mobile','o.address'
            ,'o.create_time','o.order_from','o.sync_status'
            ,'o.begin_time as order_begin_time','o.end_time as order_end_time'
            ,'og.goods_title'
            ,'s.shopname','m.mobile as alipay_mobile'
            ,'ol.id as lease_id','ol.which_period','ol.rent','ol.paid_rent','ol.begin_time'
            ,'ol.end_time','ol.status as lease_status','ol.pay_time','ol.pay_type','ol.settle_amount'
            ,'ol.settle_type','ol.settle_time','ol.reason','ol.withhold_status','ol.overdue_days','ol.refund_time as trans_create_time'
        );
        $handel->where($where);
        $handel->orderBy('o.id','desc');
        $list = $handel->get();
        $list = obToArray($list);
        $data = [];
        foreach ($list as $k=>$item){
            $create_time = dateFormat($item['create_time']);
            $begin_time = dateDFormat($item['begin_time']);
            $pay_time = dateFormat($item['pay_time']);
            //订单来源
            $source = Source::where(['source_code'=>$item['order_from'],'status'=>Source::SOURCE_STATUS_ENABLE])->value('source_name');

            if($item['lease_status'] == OrderLease::ORDER_LEASE_STATUS_REFUNDED){
                if($item['settle_type'] == OrderLease::ORDER_LEASE_SETTLE_STATUS_YES){
                    $item['trans_amount'] = ShopTrans::where(['trans_type'=>ShopTrans::ORDER_TRANS_TYPE_REFUND,'order_lease_id'=>$item['lease_id']])->value('amount');
                }else{
                    $item['trans_amount'] = $item['paid_rent'];
                }
            }else{
                $item['trans_create_time'] = '';
                $item['trans_amount'] = '';
            }

            $withhold_num = OtherWithholdLog::where(['lease_id'=>$item['lease_id']])->count();
            $authenticLog = obToArray(AuthenticLog::where(['order_id'=>$item['order_id']])->select('cert_no','cert_name')->first());
            $authenticLog = obToArray($authenticLog);
            //客服备注
            $orderRemark = OrderRemark::getRemark($item['order_id']);
            if($orderRemark){
                $orderRemark = $orderRemark['create_time'].$orderRemark['username'].$orderRemark['remark'];
            }else{
                $orderRemark = '';
            }

            $orderPayType = isset(Order::ORDER_PAY_TYPE[$item['order_pay_type']]) ? Order::ORDER_PAY_TYPE[$item['order_pay_type']] : '';
            if($item['order_pay_type'] == Order::ORDER_PAY_TYPE_ZM){
                if(OrderLease::where([['order_id',$item['order_id']],['pay_type',OrderLease::LEASE_PAY_TYPE_ANTCHAIN]])->count() > 0){
                    $orderPayType = '智能合同代扣转预授权';
                }
            }
            $leasePayType = isset(OrderLease::ORDER_LEASE_PAY_TYPE_LIST[$item['pay_type']]) ? OrderLease::ORDER_LEASE_PAY_TYPE_LIST[$item['pay_type']] : '';
            $shopname = $item['shopname'];

            $month_total = $item['month_total'];
            if($item['which_period'] > $item['month_total']){
                $month_total = $item['which_period'];
            }
            $data[] = [
                'lease_id' => ' '.$item['lease_id'],
                'trade_no' => ' '.$item['trade_no'],
                'goods_title' => $item['goods_title'],
                'amount' => $item['amount'],
                'source' => $source,
                'total_lease' => $item['which_period'].'/'.$month_total.'期',
                'lease_team' => $item['order_begin_time'].'至'.$item['order_end_time'],
                'pay_type' => $orderPayType.'/'.$leasePayType,
                'create_time' => $create_time,
                'begin_time' => $begin_time,
                'pay_time' => $pay_time,
                'rent' => $item['rent'],
                'paid_rent' => $item['paid_rent'],
                'refund' => $item['trans_amount'],
                'settle_amount' => $item['settle_amount'],
                'settle_time' => $item['settle_time'],
                'withhold_num' => $withhold_num,
                'lease_status' => isset(OrderLease::ORDER_LEASE_STATUS_LIST[$item['lease_status']]) ? OrderLease::ORDER_LEASE_STATUS_LIST[$item['lease_status']] : '',
                'status' => isset(OrderService::ORDER_STATUS_NAME_LIST[$item['status']]) ? OrderService::ORDER_STATUS_NAME_LIST[$item['status']] : '',
                'trans_create_time' => $item['trans_create_time'],
                'cert_name' => isset($authenticLog['cert_name']) ? $authenticLog['cert_name'] : '',
                'alipay_mobile' => $item['alipay_mobile'],
                'realname' => $item['realname'],
                'mobile' => ' '.$item['mobile'],
                'prov' => $item['prov'],
                'city' => $item['city'],
                'area' => $item['area'],
                'address' => $item['address'],
                'remark' => $orderRemark,
                'shopname' => $shopname
            ];
        }
        return $data;
    }

    /**
     * 管理后台财务逾期列表导出
     * @param $request
     * @return array
     */
    public static function adminFinanceOverdueListExport($request,$shopId=''){
        $orderNo = $request->input('orderNo','');
        $goodsName = $request->input('goodsName','');
        $receivingName = $request->input('receivingName','');
        $receivingMobile = $request->input('receivingMobile','');
        $shopName = $request->input('shopName','');
        $leaseTime = $request->input('leaseTime','');
        $overdueDay = $request->input('overdueDay','');
        $sendStatus = $request->input('sendStatus','');
        $where[] = ['ol.status',OrderLease::ORDER_LEASE_STATUS_OVERDUE];
        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($goodsName){
            $where[] = ['og.goods_title','like','%'.$goodsName.'%'];
        }
        if($receivingName){
            $where[] = ['o.realname','like','%'.$receivingName.'%'];
        }
        if($receivingMobile){
            $where[] = ['o.mobile','like','%'.$receivingMobile.'%'];
        }
        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }
        if($sendStatus){
            $where[] = ['ol.msg_status',$sendStatus];
        }
        if($overdueDay){
            $overdueDay = explode('~', $overdueDay);
            $where[] = ['ol.overdue_days','>=',$overdueDay[0]];
            $where[] = ['ol.overdue_days','<=',$overdueDay[1]];
        }
        if($leaseTime){
            $leaseTime = explode('~', $leaseTime);
            $where[] = ['ol.begin_time','>=',strtotime($leaseTime[0])];
            $where[] = ['ol.begin_time','<=',strtotime($leaseTime[1])];
        }

        $handel = Order::from('sp_order_2 as o');
        $handel->leftjoin('sp_order_lease_2 as ol','o.id','ol.order_id');
        $handel->leftjoin('sp_order_goods_2 as og','o.id','og.order_id');
        $handel->leftjoin('sp_member_2 as u','o.uid','u.id');
        $handel->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id');
        $handel->select(
            'o.id as order_id','o.trade_no','o.rent_total','o.month_total','o.status'
            ,'o.prov','o.city','o.area'
            ,'o.realname','o.mobile','o.address'
            ,'o.create_time','o.amount'
            ,'o.begin_time as order_begin_time','o.end_time as order_end_time'
            ,'og.goods_title'
            ,'s.shopname','u.cert_name'
            ,'ol.id as lease_id','ol.which_period','ol.rent','ol.paid_rent','ol.begin_time'
            ,'ol.end_time','ol.status as lease_status','ol.pay_time','ol.pay_type','ol.withhold_status','ol.overdue_days','ol.overdue_amount'
            ,'ol.msg_status as send_status'
        );
        $handel->where($where);
        $handel->orderBy('o.id','desc');
        $list = $handel->get();
        $list = obToArray($list);
        $data = [];
        foreach ($list as $k=>$item){
            $item['sms_num'] = 0;
            if($item['send_status'] == OrderLease::ORDER_LEASE_OVERDUE_SEND_YES){
                $item['sms_num'] = SmsRecord::where(['template_id'=>SmsTemplate::SMS_TEMPLATE_ORDER_COLLECTION,'lease_id'=>$item['lease_id']])->count('id');
            }
            $item['begin_time'] = dateDFormat($item['begin_time']);
            $item['withhold_num'] = OtherWithholdLog::where(['lease_id'=>$item['lease_id']])->count();
            //订单状态
            $item['status'] = isset(OrderService::ORDER_STATUS_NAME_LIST[$item['status']]) ? OrderService::ORDER_STATUS_NAME_LIST[$item['status']] : '';
            $data[] = [
                'trade_no'=>' '.$item['trade_no'],
                'goods_title'=>$item['goods_title'],
                'amount'=>$item['amount'],
                'which_period'=>$item['which_period'].'/'.$item['month_total'],
                'lease_total'=>$item['order_begin_time'].'至'.$item['order_end_time'],
                'begin_time'=>$item['begin_time'],
                'rent'=>$item['rent'],
                'paid_rent'=>$item['paid_rent'],
                'withhold_num'=>$item['withhold_num'],
                'realname'=>$item['cert_name'],
                'mobile'=>' '.$item['mobile'],
                'status'=>$item['status'],
                'lease_status'=>isset(OrderLease::ORDER_LEASE_STATUS_LIST[$item['lease_status']]) ? OrderLease::ORDER_LEASE_STATUS_LIST[$item['lease_status']] : '',
                'shopname'=>$item['shopname'],
                'sms_num'=>$item['sms_num'],
                'realnameadd'=>$item['realname'],
                'prov'=>$item['prov'],
                'city'=>$item['city'],
                'area'=>$item['area'],
                'address'=>$item['address'],
                'overdue_days'=>$item['overdue_days'],
                'send_status'=>OrderLease::ORDER_LEASE_OVERDUE_SEND_LIST[$item['send_status']],
            ];
        }
        return $data;
    }

    /**
     * 管理后台财务对账流水导出
     * @param $request
     * @return array|false|string
     */
    public static function adminFinanceReconciliationExport($request,$shopId=''){
        $orderNo = $request->input('orderNo','');
        $shopName = $request->input('shopName','');
        $moneyFlow = $request->input('moneyFlow','');
        $flowType = $request->input('flowType','');
        $createTime = $request->input('createTime','');
        $where = [];
        if($shopId){
            $where[] = ['t.member_id',$shopId];
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($shopName){
            $member_id = ShopUser::where([['shopname','like','%'.$shopName.'%']])->value('member_id');
            $where[] = ['t.member_id',$member_id];
        }
        if($moneyFlow){
            if($moneyFlow == 1){
                $where[] = ['t.amount','>',0];
            }else{
                $where[] = ['t.amount','<=',0];
            }
        }
        if($flowType){
            $where[] = ['t.trans_type',$flowType];
        }
        if($createTime){
            $createTime = explode('~', $createTime);
            $where[] = ['t.create_time','>=',$createTime[0]];
            $where[] = ['t.create_time','<=',$createTime[1]];
        }

        $list = ShopTrans::from('sp_shop_trans as t')
            ->leftjoin('sp_order_2 as o','o.id','t.order_id')
            ->leftjoin('sp_order_goods_2 as og','o.id','og.order_id')
            ->select(
                'o.id as order_id','o.trade_no','o.seller_id','o.realname','o.pay_type','o.sync_status','o.uid','o.rent_total'
                ,'t.id as trans_id','t.amount','t.before_balance','t.after_balance',
                't.service_fee','t.trans_type','t.create_time','t.remarks','t.order_lease_id','t.member_id'
                ,'t.order_fixed_cost','t.order_rate','t.status','og.is_buy_out','og.pact_type',
                'og.setmeal_foregift','o.amount','o.is_insurance_choose','o.is_insurance_choose_custom'
            )
            ->where($where)
            ->orderBy('o.id','desc')
            ->get();
        $list = obToArray($list);

        $data = [];
        foreach ($list as $k=>$item){
            $lease = [];
            $orderLeasePayType = '';
            $orderPayType = isset(Order::ORDER_PAY_TYPE[$item['pay_type']]) ? Order::ORDER_PAY_TYPE[$item['pay_type']] : '';
            if($item['pay_type'] == Order::ORDER_PAY_TYPE_ZM){
                if(OrderLease::where([['order_id',$item['order_id']],['pay_type',OrderLease::LEASE_PAY_TYPE_ANTCHAIN]])->count() > 0){
                    $orderPayType = '智能合同代扣转预授权';
                }
            }
            if($item['order_lease_id']){
                $lease = OrderLease::where(['id'=>$item['order_lease_id']])->select('which_period','rent','paid_rent','settle_amount','pay_type')->first();
                $lease =obToArray($lease);
                $orderLeasePayType = isset(OrderLease::ORDER_LEASE_PAY_TYPE_LIST[$lease['pay_type']]) ? OrderLease::ORDER_LEASE_PAY_TYPE_LIST[$lease['pay_type']] : '';
            }
            if($item['trans_type'] == ShopTrans::ORDER_TRANS_TYPE_BUYOUT){
                $byOutRent = OrderBuyout::where(['order_id'=>$item['order_id']])->value('rent');
                $lease['rent'] = $byOutRent;
                $lease['paid_rent'] = $byOutRent;
                $lease['settle_amount'] = $byOutRent;
            }
            $shopName = ShopUser::where(['member_id'=>$item['member_id']])->value('shopname');
            $detail = obToArray(TransDetail::where(['trans_id'=>$item['trans_id']])->select('realname_amount','contract_amount','risk_amount')->first());
            $PayType = !empty($orderLeasePayType)?$orderPayType.'/'.$orderLeasePayType:'';
            if($item['pact_type']==1){
                $order_total_price =bcadd($item['setmeal_foregift']*$item['amount'],GoodsService::getGoodsInsurance($item['is_insurance_choose'],$item['is_insurance_choose_custom']),2);
            }else{
                $order_total_price =$item['rent_total'];
            }

            $order_fee = isset($detail['realname_amount'])&&$detail['realname_amount']>0 ? sprintf("%.4f", ($item['order_rate'] * $order_total_price/100)) : 0;

            $data[] = [
                'trans_id'=>$item['trans_id'],
                'shopname'=>$shopName,
                'account_type'=>'租金账户',
                'order_no'=>$item['trade_no'],
                'pact_type'=>' '.$item['pact_type']==1?'固定租期':'租完即送',
                'order_total_price'=>$order_total_price,
                'which_period'=>isset($lease['which_period']) ? $lease['which_period'] : '',
                'rent'=>isset($lease['rent']) ? $lease['rent'] : '',
                'paid_rent'=>isset($lease['paid_rent']) ? $lease['paid_rent'] : '',
                'pay_type' => $PayType,
                'order_rate'=>$item['order_rate'],
                'order_fee'=>$order_fee,
                'order_fixed_cost'=>$item['order_fixed_cost'],
                'realname_amount'=>isset($detail['realname_amount']) ? $detail['realname_amount'] : 0,
                'contract_amount'=>isset($detail['contract_amount']) ? $detail['contract_amount'] : 0,
                'risk_amount'=>isset($detail['risk_amount']) ? $detail['risk_amount'] : 0,
                'total_fee'=>$item['service_fee'],
                'settle_amount'=>isset($lease['settle_amount']) ? $lease['settle_amount'] : '',
                'amount'=>$item['amount'],
                'before_balance'=>$item['before_balance'],
                'after_balance'=>$item['after_balance'],
                'trans_type'=>isset(ShopTrans::ORDER_TRANS_TYPE_LIST[$item['trans_type']]) ? ShopTrans::ORDER_TRANS_TYPE_LIST[$item['trans_type']] : '',
                'remarks'=>$item['remarks'],
                'trade_no'=>' '.$item['trade_no'],
                'cert_name'=>MemberOther::where('id',$item['uid'])->value('cert_name'),
                'realname'=>$item['realname'],
                'create_time'=>$item['create_time'],
                'status'=>$item['status'] == ShopTrans::ORDER_STATUS_FINISH ? '已到账' : '未到账',
            ];
        }
        return $data;
    }

    /**
     * 管理后台订单列表导出
     * @param $request
     * @return array|false|string
     */
    public static function adminOrderListExport($request,$shopId='',$where=[],$sale_user_id=''){
        $orderStatus = $request->input('orderStatus','');
        $orderNo = $request->input('orderNo','');
        $goodsName = $request->input('goodsName','');
        $receivingName = $request->input('receivingName','');
        $receivingMobile = $request->input('receivingMobile','');
        $riskName = $request->input('riskName','');
        $riskMobile = $request->input('riskMobile','');
        $shopName = $request->input('shopName','');
        $orderCreateTime = $request->input('orderCreateTime','');
        $orderStatusGroup = $request->input('orderStatusGroup','');
        $roleId= $request->input('role_id', '');
        $sale_user_id_order=$request->input('sale_user_id', '');

        if($sale_user_id_order&&!is_array($sale_user_id_order)){
            $where[] = ['o.sale_user_id',$sale_user_id_order];
        }
        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        $status = [];
        $groupStatus = [];
        if($orderStatus && $orderStatus != OrderService::ORDER_LIST_STATUS_ALL){
            $status = explode(',', $orderStatus);
        }

        if ($orderStatus) {
            if($roleId&&$orderStatus==OrderService::ORDER_STATUS_REPAYMENT){
                $where[] = ['o.status', OrderService::ORDER_STATUS_REPAYMENT];
            }else  if($roleId&&$orderStatus==OrderService::ORDER_STATUS_OVERDUE){
                $where[] = ['o.status', OrderService::ORDER_STATUS_OVERDUE];
            }else{
                $where[] = ['o.status', $orderStatus];
            }
        }else if($roleId){
            $where[] = ['o.status', OrderService::ORDER_STATUS_OVERDUE];
        }

        if($orderStatusGroup && $orderStatusGroup != OrderService::ORDER_LIST_STATUS_ALL){
            $groupStatus = OrderService::getOrderStatus($orderStatusGroup);
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($goodsName){
            $where[] = ['og.goods_title','like','%'.$goodsName.'%'];
        }
        if($receivingName){
            $where[] = ['o.realname','like','%'.$receivingName.'%'];
        }
        if($receivingMobile){
            $where[] = ['o.mobile',$receivingMobile];
        }
        if($riskName){
            $where[] = ['m.cert_name','like','%'.$riskName.'%'];
        }
        if($riskMobile){
            $where[] = ['m.mobile',$riskMobile];
        }
        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }
        if($orderCreateTime){
            $orderCreateTime = explode('~', $orderCreateTime);
            $where[] = ['o.create_time','>=',strtotime($orderCreateTime[0])];
            $where[] = ['o.create_time','<=',strtotime($orderCreateTime[1])];
        }

        $whereIn = [];

        if($status){
            $whereIn = $status;
        }
        if($groupStatus){
            $whereIn = $groupStatus;
        }

        $orderList = Order::from('sp_order_2 as o')
            ->leftjoin('sp_order_goods_2 as og','o.id','og.order_id')
            ->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id')
            ->leftjoin('sp_member_2 as m','o.uid','m.id')
            ->select(
                'o.id','o.status','o.trade_no','o.rent_total','o.month_total','o.amount','o.pay_type','o.foregift','o.foregift_deduct'
                ,'o.remark','o.realname','o.mobile as address_mobile','o.address','o.sign_status','o.guarantee_status','o.pact_url','o.is_skip','o.order_from'
                ,'o.prov','o.city','o.area','o.begin_time','o.end_time','o.create_time','o.rights_auth_no','o.overdue_days','o.sale_user_id'
                ,'og.goods_title','og.imgurl','og.setmeal_title','og.is_buy_out','og.device_number','og.setmeal_price'
                ,'m.mobile','m.cert_name','m.cert_no','o.is_lock','m.idcard_front','m.idcard_back','m.gender','og.acquisition_price'
                ,'s.shopname','o.reduced_status','o.reduced_price','o.phone_brand'
            )
            ->where($where);
        if($sale_user_id&&count(explode(',',$sale_user_id))>1){
            $orderList->whereIn('o.sale_user_id', explode(',',$sale_user_id));
        }
        if($sale_user_id_order&&is_array($sale_user_id_order)){
            $orderList->whereIn('o.sale_user_id',$sale_user_id_order);
        }
        $whereIn && $orderList->whereIn('o.status',$whereIn);
        $orderList->orderBy('o.create_time','desc');
        $list = obToArray($orderList->get());
        $data = [];
        foreach ($list as $k=>$value){
            //订单来源
            $TwoElementsList='';
            $TwoElements=TwoElements::where('order_id',$value['id'])->get()->toArray();
            if($TwoElements){
                foreach ($TwoElements as $k1=>$v1){
                    $TwoElementsList=$TwoElementsList.$v1['name'].$v1['phone'].'/';
                }
            }
            $OrderRemarklist=OrderRemark::where('order_id',$value['id'])->get()->toArray();
            if($OrderRemarklist){
                foreach($OrderRemarklist as $k2=>$v2){
                    $TwoElementsList=$TwoElementsList.$v2['remark'].'/';
                }
            }


            $source = Source::where(['source_code'=>$value['order_from'],'status'=>Source::SOURCE_STATUS_ENABLE])->select('source_name')->value('source_name');
            if($source == '推广平台-线上'){
                $where = [];
                $where[] = ['sale_user_id',$value['sale_user_id']];
                $sale_user = SaleUser::GetSaleUser($where);
                $source .= $sale_user['faname'];
            }
            $channel_name=SaleUser::where('sale_user_id',$value['sale_user_id'])->value('channel_name');
            if($channel_name){
                $source=$source.'-'.$channel_name;
            }
            //客服备注
            $orderRemark = OrderRemark::getRemark($value['id']);
            if($orderRemark){
                $orderRemark = $orderRemark['create_time'].$orderRemark['username'].$orderRemark['remark'];
            }else{
                $orderRemark = '';
            }
            //退款金额
            $orderRefundAmount = OrderRefund::where(['order_id'=>$value['id'],'refund_status'=>OrderRefund::ORDER_REFUND_SUCCESS])->sum('refund_amount');
            $paid_lease = OrderLease::where([['order_id',$value['id']],['status',OrderLease::ORDER_LEASE_STATUS_PAID]])->select('id','rent','status')->get();
            $paid_lease = obToArray($paid_lease);
            $paid_total_rent = 0;
            $paid_count = 0;


            $overdue_amount= OrderLease::where([['order_id',$value['id']],['status',OrderLease::ORDER_LEASE_STATUS_OVERDUE]])->sum('overdue_amount');
            $overdue_days= OrderLease::where([['order_id',$value['id']],['status',OrderLease::ORDER_LEASE_STATUS_OVERDUE]])->sum('overdue_days');

            $overdue_rent_amount= OrderLease::where([['order_id',$value['id']],['status',OrderLease::ORDER_LEASE_STATUS_OVERDUE]])->sum('rent');

            if(!empty($paid_lease)){
                $paid_total_rent = array_sum(array_column($paid_lease,'rent'));
                if($value['reduced_status']){
                    $paid_total_rent=bcadd($paid_total_rent,$value['reduced_price'],2);
                }
                $paid_count = count($paid_lease);
            }
            $buyout_amount = sprintf("%.2f",$value['setmeal_price']*$value['amount']-$paid_total_rent);
            $type_remark = "";
            $pay_remark = "";
            if($value['pay_type'] == Order::ORDER_PAY_TYPE_ZM){
                $type_remark = $value['foregift'] > 0 ? "(部分全免)" : "(押金全免)";
                if($value['rights_auth_no']){
                    $pay_remark = "(已授权代扣)";
                }
            }
            $every_month_lease = OrderLease::where([['order_id',$value['id']],['which_period',2]])->value('rent');

            $data [] =[
                'trade_no'=>' '.$value['trade_no'],
                'create_time'=>dateFormat($value['create_time']),
                'goods_title'=>$value['goods_title'].'/'.$value['setmeal_title'],
                'amount'=>$value['amount'],
                'order_from'=>$source,
                'is_lock'=>$value['is_lock']?'有锁':'无锁',
                'purchase_price'=>$value['acquisition_price'],
                'rent_total'=>$value['rent_total'],
                'month_total'=>$value['month_total'],
                'every_month_lease'=>$every_month_lease,
                'pay_rent_total'=>$paid_total_rent,
                'pay_month_total'=>$paid_count,
                'month_time'=>$value['begin_time'].'-'.$value['end_time'],
                'order_pay_type'=>isset(Order::ORDER_PAY_TYPE[$value['pay_type']]) ? Order::ORDER_PAY_TYPE[$value['pay_type']] : ''.$type_remark,
                'foregift_deduct'=>$value['foregift_deduct'],
                'foregift'=>$value['foregift'],
                'pay_type'=>isset(Order::ORDER_PAY_TYPE[$value['pay_type']]) ? Order::ORDER_PAY_TYPE[$value['pay_type']] : ''.$pay_remark,
                'status'=>isset(OrderService::ORDER_STATUS_NAME_LIST[$value['status']]) ? OrderService::ORDER_STATUS_NAME_LIST[$value['status']] : '',
                'cert_name'=>$value['cert_name'],
                'gender'=>$value['gender'],
                'cert_no'=>' '.$value['cert_no'],
                'old'=>getAgeByID($value['cert_no']),
                'mobile'=>' '.$value['mobile'],
                'realname'=>$value['realname'],
                'address_mobile'=>' '.$value['address_mobile'],
                'prov'=>$value['prov'],
                'city'=>$value['city'],
                'area'=>$value['area'],
                'address'=>$value['address'],
                'remark'=>$value['remark'],
                'kfremark'=>$orderRemark,
                'overdue_days'=>$value['overdue_days'],
                'overdue_rent_amount'=>$overdue_rent_amount,
                'buyout_amount'=>$buyout_amount>0?$buyout_amount:'',
                'refund_amount'=>$orderRefundAmount,
                'xz'=>'无',
                'shopname'=>$value['shopname'],
                'overdue_amount'=>$overdue_amount,
                'overdue_daysa'=>$overdue_days,
                'connection'=>$TwoElementsList,
                'phonebrand'=>$value['phone_brand'],
            ];
        }
        return $data;
    }


    /**
     * 管理后台订单分期明细导出
     * @param $request
     * @return array|false|string
     */
    public static function adminOrderListLeaseExport($request,$shopId='',$where=[]){
        $orderStatus = $request->input('orderStatus','');
        $orderNo = $request->input('orderNo','');
        $goodsName = $request->input('goodsName','');
        $receivingName = $request->input('receivingName','');
        $receivingMobile = $request->input('receivingMobile','');
        $riskName = $request->input('riskName','');
        $riskMobile = $request->input('riskMobile','');
        $shopName = $request->input('shopName','');
        $orderCreateTime = $request->input('orderCreateTime','');
        $orderStatusGroup = $request->input('orderStatusGroup','');
        $orderPayTime = $request->input('orderPayTime','');

        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        $status = [];
        $groupStatus = [];
        if($orderStatus && $orderStatus != OrderService::ORDER_LIST_STATUS_ALL){
            $status = explode(',', $orderStatus);
        }
        if($orderStatusGroup && $orderStatusGroup != OrderService::ORDER_LIST_STATUS_ALL){
            $groupStatus = OrderService::getOrderStatus($orderStatusGroup);
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($goodsName){
            $where[] = ['og.goods_title','like','%'.$goodsName.'%'];
        }
        if($receivingName){
            $where[] = ['o.realname','like','%'.$receivingName.'%'];
        }
        if($receivingMobile){
            $where[] = ['o.mobile',$receivingMobile];
        }
        if($riskName){
            $where[] = ['m.cert_name','like','%'.$riskName.'%'];
        }
        if($riskMobile){
            $where[] = ['m.mobile',$riskMobile];
        }
        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }
        if($orderCreateTime){
            $orderCreateTime = explode('~', $orderCreateTime);
            $where[] = ['o.create_time','>=',strtotime($orderCreateTime[0])];
            $where[] = ['o.create_time','<=',strtotime($orderCreateTime[1])];
        }

        if($orderPayTime){
            $orderPayTime = explode('~', $orderPayTime);
            $where[] = ['o.pay_time','>=',strtotime($orderPayTime[0])];
            $where[] = ['o.pay_time','<=',strtotime($orderPayTime[1])];
        }
//        $where[] = ['sf.sign_proof',null];

        $whereIn = [];

        if($status){
            $whereIn = $status;
        }
        if($groupStatus){
            $whereIn = $groupStatus;
        }

        $orderList = Order::from('sp_order_2 as o')
            ->leftjoin('sp_order_goods_2 as og','o.id','og.order_id')
            ->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id')
            ->leftjoin('sp_member_2 as m','o.uid','m.id')
            ->leftjoin('sp_goods_2 as g','og.goods_id','g.id')

            //            ->leftjoin('sp_order_return_2 as sf','o.id','sf.order_id')
            ->select(
                'o.id','o.status','o.trade_no','o.rent_total','o.month_total','o.amount','o.pay_type','o.foregift','o.foregift_deduct'
                ,'o.remark','o.realname','o.mobile as address_mobile','o.address','o.sign_status','o.guarantee_status','o.pact_url','o.is_skip','o.order_from'
                ,'o.prov','o.city','o.area','o.begin_time','o.end_time','o.create_time','o.rights_auth_no','o.overdue_days','o.sale_user_id'
                ,'og.goods_title','og.imgurl','og.setmeal_title','og.is_buy_out','og.device_number','og.setmeal_price','o.pay_time','o.username','o.is_insurance_buy'
                ,'o.is_insurance_choose','o.is_insurance_choose_custom' ,'m.mobile','m.cert_name','m.cert_no','m.idcard_front','m.idcard_back','m.gender','o.is_lock'
                ,'s.shopname','g.new_grade','og.pact_type','o.reduced_status','o.reduced_price','og.acquisition_price'
            )
            ->where($where);
        $whereIn && $orderList->whereIn('o.status',$whereIn);
        $orderList->orderBy('o.pay_time','desc');
        $list = obToArray($orderList->get());
        $data = [];
        foreach ($list as $k=>$value){
            //订单来源
            $source = Source::where(['source_code'=>$value['order_from'],'status'=>Source::SOURCE_STATUS_ENABLE])->select('source_name')->value('source_name');
            if($source == '推广平台-线上'){
//                $where = [];
//                $where[] = ['sale_user_id',$value['sale_user_id']];
//                $sale_user = SaleUser::GetSaleUser($where);
                $source .= $value['sale_user_id'];
            }
            //客服备注
            $orderRemarkFind = OrderRemark::getRemark($value['id']);
            if($orderRemarkFind){
                $orderRemark = $orderRemarkFind['create_time'].$orderRemarkFind['username'].$orderRemarkFind['remark'];
            }else{
                $orderRemark = '';
            }
            //退款金额
            $orderRefundAmount = OrderRefund::where(['order_id'=>$value['id'],'refund_status'=>OrderRefund::ORDER_REFUND_SUCCESS])->sum('refund_amount');
            $paid_lease = OrderLease::where([['order_id',$value['id']],['status',OrderLease::ORDER_LEASE_STATUS_PAID]])->select('id','rent','status')->get();
            $paid_lease = obToArray($paid_lease);
            $paid_total_rent = 0;
            $paid_count = 0;


            if(!empty($paid_lease)){
                $paid_total_rent = array_sum(array_column($paid_lease,'rent'));
                if($value['reduced_status']){
                    $paid_total_rent=bcadd($paid_total_rent,$value['reduced_price'],2);
                }
                $paid_count = count($paid_lease);
            }
            $all_paid_lease = OrderLease::where('order_id',$value['id'])->select('id','rent','status','pay_time','paid_rent')->get();
            foreach ($all_paid_lease as $item) {
                $item['pay_time']=$item['pay_time']?date('Y-m-d H:i:s',$item['pay_time']):'';
            }

            $all_paid_total_rent = array_sum(array_column(obToArray($all_paid_lease),'rent'));

            $buyout_amount = $all_paid_total_rent-$paid_total_rent;

//            "下单日期",
//            "支付日期",
//            "平台",
//            "姓名",
//            "订单号",
//            "型号",
//            "新机/二手",
//            "审核人",
//            "总回款",
//            "拿货总价",
//            "期数",
//            "用户付款金额",
//            "用户付款期数",
//            "是否有锁","意外保障金","流量费","运营成本","实际支付成本","是否买断","剩余应还款","一期回款（不含保险）","第二期","应回1","回款1",
//            "第三期","应回2","回款2","第四期","应回3","回款3","第五期","应回4","回款4","第六期","应回5","回款5","第七期","应回6","回款6","第八期",
//            "应回7","回款7","第九期","应回8","回款8","第十期","应回9","回款9","第十一期","应回10","回款10","第十二期","应回11","回款11","买断/卖","总回款"

            $data [] =[
                'create_time'=>dateFormat($value['create_time']),
                'pay_time'=>dateFormat($value['pay_time']),
                'shopname'=>$value['shopname'],
                'realname'=>$value['cert_name'],
                'trade_no'=>' '.$value['trade_no'],
                'goods_title'=>$value['goods_title'].'/'.$value['setmeal_title'],
                'new_grade_title'=>GoodsService::getGoodsNewGrade($value['new_grade']),
                'aduit_person'=>$orderRemarkFind['username']??'',
                'all_paid_total_rent'=>$all_paid_total_rent,
                'pick_pirce'=>$value['acquisition_price'],
                'month_total'=>$value['month_total'],
                'pay_rent_total'=>$paid_total_rent,
                'pay_month_total'=>$paid_count,
                'is_lock'=>$value['is_lock']?'有锁':'无锁',
                'is_insurance_name'=> GoodsService::getGoodsInsuranceName($value['is_insurance_choose']),
                'flow_price'=>'',
                'operating_costs'=>'',
                'Actual_cost_paid'=>'',
                'is_buy_out'=>GoodsService::getGoodsPactName($value['pact_type']),
                'Balance_to_repaid'=>$buyout_amount,
                'one'=>$all_paid_lease[0]['rent'],
                'two'=>$all_paid_lease[1]['paid_rent']??'',
                'two_should'=>$all_paid_lease[1]['rent']??'',
                'two_time'=>$all_paid_lease[1]['pay_time']??'',
                'thr'=>$all_paid_lease[2]['paid_rent']??'',
                'thr_should'=>$all_paid_lease[2]['rent']??'',
                'thr_time'=>$all_paid_lease[2]['pay_time']??'',
                'four'=>$all_paid_lease[3]['paid_rent']??'',
                'four_should'=>$all_paid_lease[3]['rent']??'',
                'four_time'=>$all_paid_lease[3]['pay_time']??'',
                'firth'=>$all_paid_lease[4]['paid_rent']??'',
                'firth_should'=>$all_paid_lease[4]['rent']??'',
                'firth_time'=>$all_paid_lease[4]['pay_time']??'',
                'six'=>$all_paid_lease[5]['paid_rent']??'',
                'six_should'=>$all_paid_lease[5]['rent']??'',
                'six_time'=>$all_paid_lease[5]['pay_time']??'',
                'seven'=>$all_paid_lease[6]['paid_rent']??'',
                'seven_should'=>$all_paid_lease[6]['rent']??'',
                'seven_time'=>$all_paid_lease[6]['pay_time']??'',
                'eight'=>$all_paid_lease[7]['paid_rent']??'',
                'eight_should'=>$all_paid_lease[7]['rent']??'',
                'eight_time'=>$all_paid_lease[7]['pay_time']??'',
                'nine'=>$all_paid_lease[8]['paid_rent']??'',
                'nine_should'=>$all_paid_lease[8]['rent']??'',
                'nine_time'=>$all_paid_lease[8]['pay_time']??'',
                'ten'=>$all_paid_lease[9]['paid_rent']??'',
                'ten_should'=>$all_paid_lease[9]['rent']??'',
                'ten_time'=>$all_paid_lease[9]['pay_time']??'',
                'eleventh'=>$all_paid_lease[10]['paid_rent']??'',
                'eleventh_should'=>$all_paid_lease[10]['rent']??'',
                'eleventh_time'=>$all_paid_lease[10]['pay_time']??'',
                'twl'=>$all_paid_lease[11]['paid_rent']??'',
                'twl_should'=>$all_paid_lease[11]['rent']??'',
                'twl_time'=>$all_paid_lease[11]['pay_time']??'',
                'buy_or_sale'=>'',
                'paid_total_rent'=>$paid_total_rent,
            ];
        }
        return $data;
    }


    /**
     * 管理后台订单发货列表导出
     * @param $request
     * @return array|false|string
     */
    public static function adminOrderDeliverListExport($request,$shopId='',$where=[],$sale_user_id=''){
        $orderStatus = $request->input('orderStatus','');
        $orderNo = $request->input('orderNo','');
        $goodsName = $request->input('goodsName','');
        $receivingName = $request->input('receivingName','');
        $receivingMobile = $request->input('receivingMobile','');
        $riskName = $request->input('riskName','');
        $riskMobile = $request->input('riskMobile','');
        $shopName = $request->input('shopName','');
        $orderCreateTime = $request->input('orderCreateTime','');
        $orderStatusGroup = $request->input('orderStatusGroup','');
        $orderPayTime = $request->input('orderPayTime','');
        $sale_user_id_order=$request->input('sale_user_id', '');
        if($sale_user_id_order){
            $where[] = ['o.sale_user_id',$sale_user_id_order];
        }


        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        $status = [];
        $groupStatus = [];
        if($orderStatus && $orderStatus != OrderService::ORDER_LIST_STATUS_ALL){
            $status = explode(',', $orderStatus);
        }
        if($orderStatusGroup && $orderStatusGroup != OrderService::ORDER_LIST_STATUS_ALL){
            $groupStatus = OrderService::getOrderStatus($orderStatusGroup);
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($goodsName){
            $where[] = ['og.goods_title','like','%'.$goodsName.'%'];
        }
        if($receivingName){
            $where[] = ['o.realname','like','%'.$receivingName.'%'];
        }
        if($receivingMobile){
            $where[] = ['o.mobile',$receivingMobile];
        }
        if($riskName){
            $where[] = ['m.cert_name','like','%'.$riskName.'%'];
        }
        if($riskMobile){
            $where[] = ['m.mobile',$riskMobile];
        }
        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }
        if($orderCreateTime){
            $orderCreateTime = explode('~', $orderCreateTime);
            $where[] = ['o.create_time','>=',strtotime($orderCreateTime[0])];
            $where[] = ['o.create_time','<=',strtotime($orderCreateTime[1])];
        }

        if($orderPayTime){
            $orderPayTime = explode('~', $orderPayTime);
            $where[] = ['o.pay_time','>=',strtotime($orderPayTime[0])];
            $where[] = ['o.pay_time','<=',strtotime($orderPayTime[1])];
        }
//        $where[] = ['sf.sign_proof',null];

        $whereIn = [];

        if($status){
            $whereIn = $status;
        }
        if($groupStatus){
            $whereIn = $groupStatus;
        }

        $orderList = Order::from('sp_order_2 as o')
            ->leftjoin('sp_order_goods_2 as og','o.id','og.order_id')
            ->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id')
            ->leftjoin('sp_member_2 as m','o.uid','m.id')
//            ->leftjoin('sp_order_return_2 as sf','o.id','sf.order_id')
            ->select(
                'o.id','o.uid','o.status','o.trade_no','o.rent_total','o.month_total','o.amount','o.pay_type','o.foregift','o.foregift_deduct'
                ,'o.remark','o.realname','o.mobile as address_mobile','o.address','o.sign_status','o.guarantee_status','o.pact_url','o.is_skip','o.order_from'
                ,'o.prov','o.city','o.area','o.begin_time','o.end_time','o.create_time','o.rights_auth_no','o.overdue_days','o.sale_user_id'
                ,'og.goods_title','og.imgurl','og.setmeal_title','og.setmeal_foregift','og.is_buy_out','og.device_number','og.setmeal_price','o.pay_time','o.username','o.is_insurance_buy'
                ,'o.is_insurance_choose','o.is_insurance_choose_custom','m.mobile','m.cert_name','m.cert_no','m.idcard_front','m.idcard_back','m.gender'
                ,'s.shopname','og.acquisition_price','o.reduced_status','o.reduced_price'
            )
            ->where($where);
        if($sale_user_id&&count(explode(',',$sale_user_id))>1){
            $orderList->whereIn('o.sale_user_id', explode(',',$sale_user_id));
        }
        $whereIn && $orderList->whereIn('o.status',$whereIn);
        $orderList->orderBy('o.pay_time','desc');
        $list = obToArray($orderList->get());
        $data = [];
        foreach ($list as $k=>$value){
            //订单来源
            $source = Source::where(['source_code'=>$value['order_from'],'status'=>Source::SOURCE_STATUS_ENABLE])->select('source_name')->value('source_name');
            if($value['order_from']=='saleOnline'&&$value['openId']){
                $source=$source.'-微信';
            }
            if($value['order_from']=='saleOnline'&&empty($value['openId'])){
                $source=$source.'-支付宝';
            }
            if($value['contract_no']){
                $source=$source.'-W';
            }

            $memeber= MemberOther::where('cert_no',$value['cert_no'])->orderBy('id','asc')->select('openId','id')->first();
            $first_from= Order::where('uid',$memeber['id'])->orderBy('id','asc')->value('order_from');
            $first_source = Source::where(['source_code'=>$first_from,'status'=>Source::SOURCE_STATUS_ENABLE])->select('source_name')->value('source_name');
            if($value['order_from']=='saleOnline'&&$memeber['openId']){
                $first_source=$first_source.'-微信';
            }
            if($value['order_from']=='saleOnline'&&empty($memeber['openId'])){
                $first_source=$first_source.'-支付宝';
            }
            if($value['contract_no']){
                $first_source=$first_source.'-W';
            }

            $channel_name=SaleUser::where('sale_user_id',$value['sale_user_id'])->value('channel_name');
            if($value['sale_user_id']){
                $source=$source.'-'.$value['sale_user_id'];
            }
            if($channel_name){
                $source=$source.'-'.$channel_name;
            }

            //客服备注
            $orderRemarkFind = OrderRemark::getRemark($value['id']);
            if($orderRemarkFind){
                $orderRemark = $orderRemarkFind['create_time'].$orderRemarkFind['username'].$orderRemarkFind['remark'];
            }else{
                $orderRemark = '';
            }
            //退款金额
            $orderRefundAmount = OrderRefund::where(['order_id'=>$value['id'],'refund_status'=>OrderRefund::ORDER_REFUND_SUCCESS])->sum('refund_amount');
            $paid_lease = OrderLease::where([['order_id',$value['id']],['status',OrderLease::ORDER_LEASE_STATUS_PAID]])->select('id','rent','status')->get();
            $paid_lease = obToArray($paid_lease);
            $paid_total_rent = 0;
            $paid_count = 0;

            if(!empty($paid_lease)){
                $paid_total_rent = array_sum(array_column($paid_lease,'rent'));
                if($value['reduced_status']){
                    $paid_total_rent=bcadd($paid_total_rent,$value['reduced_price'],2);
                }
                $paid_count = count($paid_lease);
            }
            $buyout_amount = sprintf("%.2f",$value['setmeal_foregift']*$value['amount']-$paid_total_rent);
            $type_remark = "";
            $pay_remark = "";
            if($value['pay_type'] == Order::ORDER_PAY_TYPE_ZM){
                $type_remark = $value['foregift'] > 0 ? "(部分全免)" : "(押金全免)";
                if($value['rights_auth_no']){
                    $pay_remark = "(已授权代扣)";
                }
            }

            //订单号	姓名	手机号码	机型	审核人	拿货价	下单日期	支付日期	是否有锁	来源	是否碎屏保	期数	预付期数 实际付款期数	已付款	实际付款	总金额	实际总金额	实际成本	剩余待还款	是否买断

            $broken=GoodsService::getGoodsInsuranceName($value['is_insurance_choose']);
            $brokeprice=GoodsService::getGoodsInsurance($value['is_insurance_choose']);

            $data [] =[
                'trade_no'=>' '.$value['trade_no'],
                'status'=>isset(OrderService::ORDER_STATUS_NAME_LIST[$value['status']]) ? OrderService::ORDER_STATUS_NAME_LIST[$value['status']] : '',
                'realname'=>$value['realname'],
                'address_mobile'=>' '.$value['address_mobile'],
                'goods_title'=>$value['goods_title'].'/'.$value['setmeal_title'],
                'aduit_person'=>$orderRemarkFind['username']??'',
                'purchase_price'=>$value['acquisition_price'],
                'create_time'=>dateFormat($value['create_time']),
                'pay_time'=>dateFormat($value['pay_time']),
                'kfremark'=>$orderRemark,
                'order_from'=>$source,
                'broken'=>$broken,
                'month_total'=>$value['month_total'],
                'month_prepay_total'=>'',
                'pay_month_total'=>$paid_count,
                'pay_rent_total'=>$paid_total_rent,
                'match_pay_rent_total'=>bcsub($paid_total_rent,$brokeprice,2),
                'rent_total'=>$value['rent_total'],
                'match_rent_total'=>bcsub($value['rent_total'],$brokeprice,2),
                'cost_price'=>'',
                'Balance_to_repaid'=>bcsub($value['rent_total'],$brokeprice+$paid_total_rent,2),
                'is_buy_out'=>$value['is_buy_out']?'可买断':'不可买断',
                'buyout_amount'=>$buyout_amount,
                'first_source'=>$first_source,
            ];
        }
        return $data;
    }

    /**
     * 管理后台订单列表导出
     * @param $request
     * @return array|false|string
     */
    public static function adminOrderRemindListExport($request,$shopId='',$where=[]){
        $orderNo = $request->input('orderNo','');
        $shopName = $request->input('shopName', '');
        $orderCreateTime= $request->input('orderCreateTime');
        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }

        $orderList = Order::from('sp_order_2 as o')
            ->leftjoin('sp_order_goods_2 as og','o.id','og.order_id')
            ->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id')
            ->leftjoin('sp_member_2 as m','o.uid','m.id')
            ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
            ->select(
                'o.id','o.status','o.trade_no','o.rent_total','o.month_total','o.amount','o.pay_type','o.foregift','o.foregift_deduct'
                ,'o.remark','o.realname','o.mobile as address_mobile','o.address','o.sign_status','o.guarantee_status','o.pact_url','o.is_skip','o.order_from'
                ,'o.prov','o.city','o.area','o.begin_time','o.end_time','o.create_time','o.rights_auth_no','o.overdue_days'
                ,'og.goods_title','og.imgurl','og.setmeal_title','og.is_buy_out','og.device_number','og.setmeal_price'
                ,'m.mobile','m.cert_name','m.cert_no','m.idcard_front','m.idcard_back','m.gender'
                ,'s.shopname',
                'ol.id as ol_id', 'ol.begin_time as ol_begin_time', 'ol.end_time as ol_end_time', 'ol.status as ol_status','o.reduced_status','o.reduced_price'
            )
            ->where($where);
//        $wherein = [0, 4];
//        $orderList->wherein('ol.status', $wherein);
        $orderList->wherein('o.status', [9,10,11,12,13,14,15,20,30,31,32,33,34,38,39,40,41,42,43,44]);
        if ($orderCreateTime) {
            $orderCreateTime = explode('~', $orderCreateTime);
            $wherebetween = [strtotime($orderCreateTime[0]), strtotime($orderCreateTime[1])];
            $orderList->wherebetween('ol.begin_time', $wherebetween);
        }else{
            $wherebetween = [strtotime(date('Y-m-d')), strtotime(date('Y-m-d 23:59:59'))];
            $orderList->wherebetween('ol.begin_time', $wherebetween);
        }
        $orderList->orderBy('ol.begin_time', 'asc');
//        $orderList->orderBy('o.create_time','desc');
        $list = obToArray($orderList->get());
        $data = [];
        foreach ($list as $k=>$value){
            //订单来源
            $source = Source::where(['source_code'=>$value['order_from'],'status'=>Source::SOURCE_STATUS_ENABLE])->select('source_name')->value('source_name');
            //客服备注
            $orderRemark = OrderRemark::getRemark($value['id']);
            if($orderRemark){
                $orderRemark = $orderRemark['create_time'].$orderRemark['username'].$orderRemark['remark'];
            }else{
                $orderRemark = '';
            }
            //退款金额
            $orderRefundAmount = OrderRefund::where(['order_id'=>$value['id'],'refund_status'=>OrderRefund::ORDER_REFUND_SUCCESS])->sum('refund_amount');
            $paid_lease = OrderLease::where([['order_id',$value['id']],['status',OrderLease::ORDER_LEASE_STATUS_PAID]])->select('id','rent','status')->get();
            $paid_lease = obToArray($paid_lease);
            $paid_total_rent = 0;
            $paid_count = 0;

            if(!empty($paid_lease)){
                $paid_total_rent = array_sum(array_column($paid_lease,'rent'));
                if($value['reduced_status']){
                    $paid_total_rent=bcadd($paid_total_rent,$value['reduced_price'],2);
                }
                $paid_count = count($paid_lease);
            }
            $buyout_amount = sprintf("%.2f",$value['setmeal_price']*$value['amount']-$paid_total_rent);
            $type_remark = "";
            $pay_remark = "";
            if($value['pay_type'] == Order::ORDER_PAY_TYPE_ZM){
                $type_remark = $value['foregift'] > 0 ? "(部分全免)" : "(押金全免)";
                if($value['rights_auth_no']){
                    $pay_remark = "(已授权代扣)";
                }
            }
            $data [] =[
                'trade_no'=>' '.$value['trade_no'],
                'create_time'=>dateFormat($value['create_time']),
                'goods_title'=>$value['goods_title'],
                'amount'=>$value['amount'],
                'order_from'=>$source,
                'rent_total'=>$value['rent_total'],
                'month_total'=>$value['month_total'],
                'pay_rent_total'=>$paid_total_rent,
                'pay_month_total'=>$paid_count,
                'month_time'=>$value['begin_time'].'-'.$value['end_time'],
                'order_pay_type'=>isset(Order::ORDER_PAY_TYPE[$value['pay_type']]) ? Order::ORDER_PAY_TYPE[$value['pay_type']] : ''.$type_remark,
                'foregift_deduct'=>$value['foregift_deduct'],
                'foregift'=>$value['foregift'],
                'pay_type'=>isset(Order::ORDER_PAY_TYPE[$value['pay_type']]) ? Order::ORDER_PAY_TYPE[$value['pay_type']] : ''.$pay_remark,
                'status'=>isset(OrderService::ORDER_STATUS_NAME_LIST[$value['status']]) ? OrderService::ORDER_STATUS_NAME_LIST[$value['status']] : '',
                'cert_name'=>$value['cert_name'],
                'mobile'=>' '.$value['mobile'],
                'realname'=>$value['realname'],
                'address_mobile'=>' '.$value['address_mobile'],
                'prov'=>$value['prov'],
                'city'=>$value['city'],
                'area'=>$value['area'],
                'address'=>$value['address'],
                'remark'=>$value['remark'],
                'kfremark'=>$orderRemark,
                'overdue_days'=>$value['overdue_days'],
                'buyout_amount'=>$buyout_amount,
                'refund_amount'=>$orderRefundAmount,
                'xz'=>'无',
                'shopname'=>$value['shopname'],
            ];
        }
        return $data;
    }
    /**
     * 管理后台订单列表导出
     * @param $request
     * @return array|false|string
     */
    public static function adminOrderRemindListBriefExport($request,$shopId='',$where=[]){
        $orderNo = $request->input('orderNo','');
        $shopName = $request->input('shopName', '');
        $orderCreateTime= $request->input('orderCreateTime');
        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }

        $orderList = Order::from('sp_order_2 as o')
            ->leftjoin('sp_order_goods_2 as og','o.id','og.order_id')
            ->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id')
            ->leftjoin('sp_member_2 as m','o.uid','m.id')
            ->leftjoin('sp_order_lease_2 as ol', 'o.id', 'ol.order_id')
            ->select(
                'o.id','o.status','o.trade_no','o.rent_total','o.month_total','o.amount','o.pay_type','o.foregift','o.foregift_deduct'
                ,'o.remark','o.realname','o.mobile as address_mobile','o.address','o.sign_status','o.guarantee_status','o.pact_url','o.is_skip','o.order_from'
                ,'o.prov','o.city','o.area','o.begin_time','o.end_time','o.create_time','o.rights_auth_no','o.overdue_days'
                ,'og.goods_title','og.imgurl','og.setmeal_title','og.is_buy_out','og.device_number','og.setmeal_price'
                ,'m.mobile','m.cert_name','m.cert_no','m.idcard_front','m.idcard_back','m.gender','o.is_lock'
                ,'s.shopname',
                'ol.id as ol_id', 'ol.rent','ol.paid_rent','ol.paid_rent','ol.which_period','ol.begin_time as ol_begin_time', 'ol.end_time as ol_end_time', 'ol.status as ol_status'
            )
            ->where($where);
        $wherein = [0,1,4];
        $orderList->wherein('ol.status', $wherein);
        $orderList->wherein('o.status', [9,10,11,12,13,14,15,20,30,31,32,33,34,40,41,42,43,44]);
        if ($orderCreateTime) {
            $orderCreateTime = explode('~', $orderCreateTime);
            $wherebetween = [strtotime($orderCreateTime[0]), strtotime($orderCreateTime[1])];
            $orderList->wherebetween('ol.begin_time', $wherebetween);
        }else{
            $wherebetween = [strtotime(date('Y-m-d')), strtotime(date('Y-m-d 23:59:59'))];
            $orderList->wherebetween('ol.begin_time', $wherebetween);
        }
        $orderList->orderBy('ol.begin_time', 'asc');
//        $orderList->orderBy('o.create_time','desc');
        $list = obToArray($orderList->get());
        $data = [];
        foreach ($list as $k=>$value){
            //客服备注
            $orderRemark = OrderRemark::getRemark($value['id']);
            if($orderRemark){
                $orderRemark = $orderRemark['create_time'].$orderRemark['username'].$orderRemark['remark'];
            }else{
                $orderRemark = '';
            }

            $data [] =[
                'shopname'=>$value['shopname'],
                'cert_name'=>$value['cert_name'],
                'mobile'=>' '.$value['mobile'],
                'rent'=>' '.$value['rent'],
                'status'=>$value['ol_status']==1?'已还款':'未还款',
                'remark'=>$value['remark'],
                'kfremark'=>$orderRemark,
                'is_lock'=>$value['is_lock']?'是':'否',
                'paid_rent'=>$value['paid_rent'],
                'which_period'=>$value['which_period'],
            ];
        }
        return $data;
    }
    /**
     * 管理后台花呗财务列表导出
     * @param $request
     * @return array|false|string
     */
    public static function adminFinanceHuaBeiListExport($request,$shopId=''){
        $orderStatus = $request->input('orderStatus');
        $orderNo = $request->input('orderNo','');
        $goodsName = $request->input('goodsName','');
        $receivingName = $request->input('receivingName','');
        $shopName = $request->input('shopName','');
        $orderCreateTime = $request->input('orderCreateTime','');
        $orderPayTime = $request->input('orderPayTime','');
        $leaseTerm = $request->input('leaseTerm','');
        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        $where[] = ['o.pay_type',Order::ORDER_PAY_TYPE_HUABEI];
        if($orderStatus){
            if($orderStatus > OrderService::ORDER_STATUS_NOT_PAY){
                $where[] = ['o.status',$orderStatus];
            }else{
                return [];
            }
        }else{
            $where[] = ['o.status','>',OrderService::ORDER_STATUS_NOT_PAY];
        }

        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($goodsName){
            $where[] = ['og.goods_title','like','%'.$goodsName.'%'];
        }
        if($receivingName){
            $where[] = ['au.cert_name','like','%'.$receivingName.'%'];
        }

        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }
        if($leaseTerm){
            $where[] = ['o.month_total',$leaseTerm];
        }
        if($orderCreateTime){
            $orderCreateTime = explode('~', $orderCreateTime);
            $where[] = ['o.create_time','>=',strtotime($orderCreateTime[0])];
            $where[] = ['o.create_time','<=',strtotime($orderCreateTime[1])];
        }

        if($orderPayTime){
            $orderPayTime = explode('~', $orderPayTime);
            $where[] = ['o.pay_time','>=',strtotime($orderPayTime[0])];
            $where[] = ['o.pay_time','<=',strtotime($orderPayTime[1])];
        }

        $handel = Order::from('sp_order_2 as o');
        $handel->leftjoin('sp_order_goods_2 as og','o.id','og.order_id');
        $handel->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id');
        $handel->leftjoin('sp_other_authentic_log as au','o.id','au.order_id');
        $handel->select(
            'o.id as order_id','o.trade_no','o.rent_total','o.month_total','o.status'
            ,'o.prov','o.city','o.area'
            ,'o.realname','o.mobile','o.address'
            ,'o.create_time','o.order_from','o.pay_time','o.alipay_trade_no','o.settlement_status','o.payment'
            ,'o.begin_time','o.end_time','o.settle_refuse_reason','o.amount','o.pay_type','o.huabei_fee_bear'
            ,'og.goods_title'
            ,'s.shopname'
            ,'au.cert_no','au.cert_name'
        );
        $handel->where($where);
        $handel->orderBy('o.id','desc');
        $list = $handel->get();
        $list = obToArray($list);
        $data = [];
        foreach ($list as $k=>$value){
            //订单状态
            $list['data'][$k]['statusName'] = isset(OrderService::ORDER_STATUS_NAME_LIST[$value['status']]) ? OrderService::ORDER_STATUS_NAME_LIST[$value['status']] : '';
            //订单来源
            $list['data'][$k]['source'] = Source::where(['source_code'=>$value['order_from'],'status'=>Source::SOURCE_STATUS_ENABLE])->value('source_name');

            $value['settlement_create_time'] = '';
            $value['settlement_amount'] = '';
            $value['service_fee'] = '';
            if($value['settlement_status'] == Order::ORDER_HUABEI_SETTLE_STATUS_YES){
                $trans = ShopTrans::where(['order_id'=>$value['order_id'],'trans_type'=>ShopTrans::ORDER_TRANS_TYPE_SETTLE])->select('amount','create_time','service_fee')->first();
                $trans = obToArray($trans);
                $value['settlement_create_time'] = isset($trans['create_time']) ? $trans['create_time'] : '';
                $value['settlement_amount'] = isset($trans['amount']) ? $trans['amount'] : '';
                $value['service_fee'] = isset($trans['service_fee']) ? $trans['service_fee'] : '';
            }

            $data [] =[
                'id'=>' '.$value['order_id'],
                'trade_no'=>' '.$value['trade_no'],
                'goods_title'=>$value['goods_title'],
                'amount'=>$value['amount'],
                'month_total'=>$value['month_total'],
                'month_time'=>dateDFormat(strtotime($value['begin_time'])).'-'.dateDFormat(strtotime($value['end_time'])),
                'order_pay_type'=>isset(Order::ORDER_PAY_TYPE[$value['pay_type']]) ? Order::ORDER_PAY_TYPE[$value['pay_type']] : '',
                'create_time'=>dateFormat($value['create_time']),
                'pay_time' => dateFormat($value['pay_time']),
                'rent_total'=>$value['rent_total'],
                'payment'=>$value['payment'],
                'huabei_fee_bear'=>$value['huabei_fee_bear'] == 1 ? '商户' : '用户',
                'service_fee'=>$value['service_fee'],
                'settlement_amount'=>$value['settlement_amount'],
                'settlement_create_time'=>$value['settlement_create_time'],
                'settlement_status'=>isset(Order::ORDER_HUABEI_SETTLE_STATUS_LIST[$value['settlement_status']]) ? Order::ORDER_HUABEI_SETTLE_STATUS_LIST[$value['settlement_status']] : '',
                'status'=>isset(OrderService::ORDER_STATUS_NAME_LIST[$value['status']]) ? OrderService::ORDER_STATUS_NAME_LIST[$value['status']] : '',
                'cert_name'=>$value['cert_name'],
                'realname'=>$value['realname'],
                'mobile'=>' '.$value['mobile'],
                'address'=>$value['prov'].$value['city'].$value['area'].$value['address'],
                'shopname'=>$value['shopname'],
            ];
        }
        return $data;
    }

    /**
     * 管理后台财务回款列表导出
     * @param $request
     * @return array|false|string
     */
    public static function adminFinanceCollectionExport($request,$shopId=''){
        $orderStatus = $request->input('orderStatus');
        $orderNo = $request->input('orderNo','');
        $goodsName = $request->input('goodsName','');
        $receivingName = $request->input('receivingName','');
        $receivingMobile = $request->input('receivingMobile','');
        $shopName = $request->input('shopName','');
        $orderCreateTime = $request->input('orderCreateTime','');
        $leaseTime = $request->input('leaseTime','');
        $orderPayTime = $request->input('orderPayTime','');
        $leaseTerm = $request->input('leaseTerm','');
        $refundTime = $request->input('refundTime','');
        $where[] = ['og.lease_set',OrderGoods::LEASE_SET_MONTH];
        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        if($orderStatus){
            if($orderStatus > OrderService::ORDER_STATUS_NOT_PAY){
                $where[] = ['o.status',$orderStatus];
            }else{
                return [];
            }
        }else{
            $where[] = ['o.status','>',OrderService::ORDER_STATUS_NOT_PAY];
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($goodsName){
            $where[] = ['og.goods_title','like','%'.$goodsName.'%'];
        }
        if($receivingName){
            $where[] = ['o.realname','like','%'.$receivingName.'%'];
        }
        if($receivingMobile){
            $where[] = ['o.mobile',$receivingMobile];
        }
        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }
        if($leaseTerm){
            $where[] = ['o.month_total',$leaseTerm];
        }
        if($orderCreateTime){
            $orderCreateTime = explode('~', $orderCreateTime);
            $where[] = ['o.create_time','>=',strtotime($orderCreateTime[0])];
            $where[] = ['o.create_time','<=',strtotime($orderCreateTime[1])];
        }
        if($leaseTime){
            $leaseTime = explode('~', $leaseTime);
            $where[] = ['ol.begin_time','>=',strtotime($leaseTime[0])];
            $where[] = ['ol.begin_time','<=',strtotime($leaseTime[1])];
        }
        if($orderPayTime){
            $orderPayTime = explode('~', $orderPayTime);
            $where[] = ['ol.pay_time','>=',strtotime($orderPayTime[0])];
            $where[] = ['ol.pay_time','<=',strtotime($orderPayTime[1])];
        }
        if($refundTime){
            //退款类型
            $where[] = ['trans.trans_type',ShopTrans::ORDER_TRANS_TYPE_REFUND];
            $refundTime = explode('~', $refundTime);
            $where[] = ['trans.create_time','>=',$refundTime[0]];
            $where[] = ['trans.create_time','<=',$refundTime[1]];
        }

        $handel = Order::from('sp_order_2 as o');
        $handel->leftjoin('sp_order_lease_2 as ol','o.id','ol.order_id');
        $handel->leftjoin('sp_order_goods_2 as og','o.id','og.order_id');
        $handel->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id');
        if($refundTime){
            $handel->leftjoin('sp_shop_trans as trans','ol.id','trans.order_lease_id');
            $handel->select(
                'o.id as order_id','o.trade_no','o.rent_total','o.month_total','o.status'
                ,'o.realname','o.mobile','o.create_time'
                ,'og.goods_title'
                ,'s.shopname'
            );
        }else{
            $handel->select(
                'o.id as order_id','o.trade_no','o.rent_total','o.month_total','o.status'
                ,'o.realname','o.mobile','o.create_time'
                ,'og.goods_title'
                ,'s.shopname'
            );
        }
        $handel->where($where);
        $handel->orderBy('o.id','desc');
        $handel->groupBy('o.id');
        $list = $handel->get();
        $list = obToArray($list);
        $dataInit = [];
        $data = [];
        for($i=1;$i<=12;$i++){
            $dataInit[$i] =  ['begin_time'=>'','rent'=>'','settle_time'=>'','settle_amount'=>''];
        }
        foreach ($list as $k=>$item){
            $item['month_rent'] = $item['rent_total'] / $item['month_total'];
            $item['create_time'] = dateFormat($item['create_time']);
            $lease = OrderLease::where(['order_id'=>$item['order_id']])->select(
                'id','which_period','rent','paid_rent','begin_time'
                ,'status','pay_time','settle_amount','settle_time'
            )->get()->toArray();
            $total_payed = 0;
            $total_pay = 0;
            $leaseData = $dataInit;
            foreach ($lease as $v){
                if(!in_array($item['status'],[OrderService::ORDER_STATUS_PART_REFUND,OrderService::ORDER_STATUS_REFUND_SUCCESS]) && $v['status'] == OrderLease::ORDER_LEASE_STATUS_PAID && isset($leaseData[$v['which_period']])){
                    $leaseData[$v['which_period']]['begin_time'] = dateDFormat($v['begin_time']);
                    $leaseData[$v['which_period']]['rent'] = $v['rent'];
                    if($v['settle_time'] && $v['settle_amount']){
                        $leaseData[$v['which_period']]['settle_time'] = $v['settle_time'];
                        $leaseData[$v['which_period']]['settle_amount'] = $v['settle_amount'];
                    }
                    $total_payed+=$v['rent'];
                }
                if($v['begin_time'] < time()){
                    $total_pay+=$v['rent'];
                }

            }
            $owe_pay = $total_pay - $total_payed;
            $item['total_pay'] = $total_pay;
            $item['owe_pay'] = $owe_pay > 0 ? $owe_pay : '';
            if(in_array($item['status'],[OrderService::ORDER_STATUS_PART_REFUND,OrderService::ORDER_STATUS_REFUND_SUCCESS,OrderService::ORDER_STATUS_COMPLETED])){
                $item['rent_total'] = '';//累计应收
                $item['total_pay'] = '';//累计应付
                $item['owe_pay'] = '';//欠收金额
            }
            if(in_array($item['status'],[OrderService::ORDER_STATUS_BUYOUT_PAY])){
                $item['total_pay'] = $item['rent_total'];
                $item['owe_pay'] = '';
            }

            $start = [
                'id'=>' '.$item['order_id'],
                'trade_no'=>' '.$item['trade_no'],
                'goods_title'=>$item['goods_title'],
                'realname'=>$item['realname'],
                'mobile'=>' '.$item['mobile'],
                'create_time'=>$item['create_time'],
                'month_total'=>$item['month_total'],
                'month_rent'=>$item['month_rent'],
            ];
            $end = [
                'rent_total'=>$item['rent_total'],
                'total_pay'=>$item['total_pay'],
                'owe_pay'=>$item['owe_pay'],
                'shopname'=>$item['shopname'],
            ];
            $leaseFormat = [];
            foreach ($leaseData as $kk=>$vv){
                foreach ($vv as $kkk=>$vvv){
                    $leaseFormat[$kkk.$kk] = $vvv;
                }
            }
            $data[] = array_merge($start,$leaseFormat,$end);
        }
        return $data;
    }

    /**
     * 管理后台财务设备分期列表导出
     * @param $request
     * @return array|false|string
     */
    public static function adminFinanceEquipmentExport($request,$shopId=''){
        $orderStatus = $request->input('orderStatus');
        $orderNo = $request->input('orderNo','');
        $goodsName = $request->input('goodsName','');
        $shopName = $request->input('shopName','');
        $orderCreateTime = $request->input('orderCreateTime','');
        $where[] = ['og.lease_set',OrderGoods::LEASE_SET_MONTH];
        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        if($orderStatus){
            if($orderStatus > OrderService::ORDER_STATUS_NOT_PAY){
                $where[] = ['o.status',$orderStatus];
            }else{
                return [];
            }
        }else{
            $where[] = ['o.status','>',OrderService::ORDER_STATUS_NOT_PAY];
        }
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($goodsName){
            $where[] = ['og.goods_title','like','%'.$goodsName.'%'];
        }

        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }

        if($orderCreateTime){
            $orderCreateTime = explode('~', $orderCreateTime);
            $where[] = ['o.create_time','>=',strtotime($orderCreateTime[0])];
            $where[] = ['o.create_time','<=',strtotime($orderCreateTime[1])];
        }

        $handel = Order::from('sp_order_2 as o');
        $handel->leftjoin('sp_order_goods_2 as og','o.id','og.order_id');
        $handel->leftjoin('sp_shop_user_2 as s','o.seller_id','s.member_id');
        $handel->select(
            'o.id as order_id','o.trade_no','o.rent_total','o.month_total','o.status'
            ,'o.create_time','o.amount','o.begin_time','o.end_time'
            ,'og.goods_title'
            ,'s.shopname'
        );
        $handel->where($where);
        $handel->orderBy('o.id','desc');
        $handel->groupBy('o.id');
        $list = $handel->get();
        $list = obToArray($list);
        $beginDate = date('Y-m-01 00:00:00', time());
        $endDate = date('Y-m-d 23:59:59', strtotime("$beginDate +1 month -1 day"));
        $data = [];
        foreach ($list as $k=>$item){
            //月租金/台
            $item['month_rent'] = $item['rent_total'] / $item['month_total'] / $item['amount'];
            //租金/台
            $item['company_rent'] = $item['rent_total'] / $item['amount'];

            $periods = 0; //当月应收期数
            $rent = 0; //当月应收金额
            $pay_money = 0;//当月实收金额
            $total_leasenum1 = 0;
            $total_paymoney2 = 0;
            $total_money1 = 0;
            $default_money = 0;
            $lease = OrderLease::where(['order_id'=>$item['order_id']])->select('id','which_period','rent','status','paid_rent','begin_time')->get();
            $lease = obToArray($lease);
            foreach ($lease as $value){
                if($value['begin_time'] >= strtotime($beginDate) && $value['begin_time'] <= strtotime($endDate)){
                    $periods = 1;
                    $rent = $value['rent'];
                    if($value['status'] != OrderLease::ORDER_LEASE_STATUS_NOPAY){
                        $pay_money = $value['rent'];
                    }
                }
                if($value['begin_time'] <= strtotime($endDate)){
                    if($value['status'] != OrderLease::ORDER_LEASE_STATUS_REFUNDED){
                        $total_leasenum1++;
                        $total_money1+=$value['rent'];
                    }
                    if($value['status'] == OrderLease::ORDER_LEASE_STATUS_PAID){
                        $total_paymoney2+=$value['rent'];
                    }
                }
                if(time() >= strtotime($item['end_time']) && $value['status'] == OrderLease::ORDER_LEASE_STATUS_NOPAY){
                    $default_money+=$value['rent'];
                }
            }

            $item['periods'] = !empty($periods) ? $periods : '';
            $item['rent'] = !empty($rent) ? $rent : '';
            $item['pay_money'] = !empty($pay_money) ? $pay_money : '';
            $overdue_money = $rent - $pay_money;
            $item['overdue_money'] = !empty($overdue_money) ? sprintf('%.2f', $overdue_money) : '';
            $total_leasenum = $total_leasenum1;
            $item['total_leasenum'] = !empty($total_leasenum) ? $total_leasenum : '';
            $total_paymoney = $total_paymoney2;
            $item['total_paymoney'] = !empty($total_paymoney) ? $total_paymoney : '';
            $total_money = $total_money1;
            $item['total_money'] = !empty($total_money) ? $total_money : '';
            $total_overduemoney = $total_money - $total_paymoney;
            $item['total_overduemoney'] = !empty($total_overduemoney) ? sprintf('%.2f', $total_overduemoney) : '';

            $receivables = $item['rent_total'] - $total_paymoney;
            $item['receivables'] = sprintf('%.2f', $receivables);
            $item['default_money'] = $default_money ? $default_money : '';
            //退款成功、买断付款、交易完成
            if(in_array($item['status'],[
                OrderService::ORDER_STATUS_PART_REFUND,
                OrderService::ORDER_STATUS_REFUND_SUCCESS,
                OrderService::ORDER_STATUS_BUYOUT_PAY,
                OrderService::ORDER_STATUS_COMPLETED,
            ])){
                $item['periods'] = ''; //当月应收期数
                $item['rent'] = ''; //当月应收金额
                $item['pay_money'] = ''; //当月实收金额
                $item['overdue_money'] = ''; //当月逾期金额=当月应收金额-当月实收金额
                $item['total_leasenum'] = ''; //累计应收期数
                $item['total_money'] = ''; //累计应收金额
                $item['total_paymoney'] = ''; //累计实收金额
                $item['total_overduemoney'] = ''; //累计逾期金额
                $item['receivables'] = ''; //应收账款=总租金-累计实收金额
                $item['default_money'] = ''; //违约金额
            }
            $data[] = [
                'id'=>' '.$item['order_id'],
                'trade_no'=>' '.$item['trade_no'],
                'goods_title'=>$item['goods_title'],
                'month_total'=>$item['month_total'],
                'month_rent'=>$item['month_rent'],
                'company_rent'=>$item['company_rent'],
                'amount'=>$item['amount'],
                'rent_total'=>$item['rent_total'],
                'create_time'=>dateFormat($item['create_time']),
                'begin_time'=>dateDFormat(strtotime($item['begin_time'])),
                'end_time'=>dateDFormat(strtotime($item['end_time'])),
                'status'=>isset(OrderService::ORDER_STATUS_NAME_LIST[$item['status']]) ? OrderService::ORDER_STATUS_NAME_LIST[$item['status']] : '',
                'periods'=>$item['periods'],
                'rent'=>$item['rent'],
                'pay_money'=>$item['pay_money'],
                'overdue_money'=>$item['overdue_money'],
                'total_leasenum'=>$item['total_leasenum'],
                'total_money'=>$item['total_money'],
                'total_paymoney'=>$item['total_paymoney'],
                'total_overduemoney'=>$item['total_overduemoney'],
                'receivables'=>$item['receivables'],
                'default_money'=>$item['default_money'],
                'shopname'=>$item['shopname'],
            ];
        }
        return $data;
    }

    /**
     * 分销商订单列表
     * @param $request
     * @param $sale_user_id
     * @param array $where
     * @return array|false|string
     */
    public static function adminOrderListSaleExport($request,$sale_user_id,$where=[]){
        $orderNo = $request->input('trade_no',''); //订单编号
        $cert_name = $request->input('cert_name',''); //下单姓名
        $name = $request->input('name',''); //下单姓名
        $phone= $request->input('phone',''); //手机号码

        $goods_name = $request->input('goods_name',''); //商品名称
        $sale_name = $request->input('sale_name',''); //推广员名称
        $begin_time = $request->input('begin_time',''); //下单时间
        $order_status = $request->input('order_status',''); //订单状态
        $page = $request->input('page',1);
        $pageSize = $request->input('pageSize',10);

        $sale_user = SaleUser::where('parent_user_id',$sale_user_id)->select('sale_user_id')->get();
        $sale_user = obToArray($sale_user);
        $sale_users = array_column($sale_user,'sale_user_id');
        $sale_users[] = $sale_user_id;
        // dd($sale_users);
        // $where[] = ['o.sale_user_id',$sale_user_id];
        if($orderNo){
            $where[] = ['o.trade_no','like','%'.$orderNo.'%'];
        }
        if($name){
            $where[] = ['m.cert_name',$name];
        }
        if($phone){
            $where[] = ['m.phone','like','%'.$phone.'%'];
        }
        if($cert_name){
            $where[] = ['m.cert_name',$cert_name];
        }
        if($goods_name){
            $where[] = ['og.goods_name','like','%'.$goods_name.'%'];
        }
        if($sale_name){
            $where[] = ['u.people',$sale_name];
        }
        if($order_status){
            $where[] = ['o.status',$order_status];
        }
        if($begin_time){
            $begin_time = explode('~', $begin_time);
            $where[] = ['o.create_time','>=',strtotime($begin_time[0])];
            $where[] = ['o.create_time','<=',strtotime($begin_time[1])];
        }

        $list = Order::from('sp_order_2 as o')
            ->select(
                'o.id as order_id','o.trade_no','o.rent_total','o.status','o.reward','o.create_time'
                ,'u.faname'
                ,'m.mobile'
                ,'m.cert_name'
                ,'og.goods_title'
            )
            ->leftjoin('sp_order_goods_2 as og','o.id','og.order_id')
            ->leftjoin('sp_member_2 as m','o.uid','m.id')
            ->leftjoin('sp_sale_user as u','o.sale_user_id','u.sale_user_id')
            ->where($where)
            ->whereIn('o.sale_user_id',$sale_users)
            ->orderBy('o.id','desc')
            ->groupBy('o.id')
            ->get();
        $list = obToArray($list);
        $data=[];
        if(isset($list) && $list){
            foreach ($list as $k=>$item){
                //订单状态
                //租期详情
                $orderLease = OrderLease::saleOrderLeaseDetail($item['order_id']);

                //总佣金
                $reward = $orderLease['reward_amount'] + $item['reward'];
                //已结算租金
                $paid_num_rent = $orderLease['paid_num_rent']?$orderLease['paid_num_rent']:'0.00';
                if($orderLease['reward'] == 1){ //结算固定金额
                    $paid_num = sprintf("%.2f",$paid_num_rent + $item['reward']);
                }else{
                    $paid_num = $paid_num_rent;
                }

                $data [] =[
                    'trade_no'=>' '.$item['trade_no'],
                    'cert_name'=> mb_substr($item['cert_name'],0,1,'utf-8').'*'.mb_substr($item['cert_name'],2,6,'utf-8'),
                    'phone'=>substr_replace($item['mobile'],'***',3,3),
                    'goods_title'=>$item['goods_title'],
                    'faname'=>$item['faname'],
                    'orderLease'=>$orderLease['orderLease'],
                    'create_time'=> date('Y-m-d H:i:s',$item['create_time']),
                    'rent_total'=>$paid_num_rent.'/'.$item['rent_total'],
                    'statusName'=> OrderService::ORDER_STATUS_NAME_LIST[$item['status']],
                    'reward'=> $paid_num.'/'.$reward,
                ];

            }
        }
        return $data;
    }
    /**
     * 管理后台融资报单列表导出
     * @param $input
     * @return array
     */
    public static function adminReportBillListExport($input,$shopId='')
    {
        $where[] = ['su.report_bill',ShopUser::SHOP_USER_REPORT_BILL_OPEN];
        $where[] = ['o.status', '>', OrderService::ORDER_STATUS_SIGN_NOTPAY];
        if($shopId){
            $where[] = ['o.seller_id',$shopId];
        }
        if (!empty($input['trade_no'])) {
            $where[] = ['o.trade_no', 'like', '%' . trim($input['trade_no']) . '%'];
        }
        if (!empty($input['goods_title'])) {
            $where[] = ['og.goods_title', 'like', '%' . trim($input['goods_title']) . '%'];
        }
        if (!empty($input['realname'])) {
            $where[] = ['o.realname', 'like', '%' . trim($input['realname']) . '%'];
        }
        if (!empty($input['mobile'])) {
            $where[] = ['o.mobile', 'like', '%' . trim($input['mobile']) . '%'];
        }
        if (!empty($input['create_time'])) {
            $time = searchTime($input['create_time']);
            $where[] = ['o.create_time', '>=', strtotime($time['begin_time'])];
            $where[] = ['o.create_time', '<=', strtotime($time['end_time'])];
        }
        if (!empty($input['status'])) {
            $where[] = ['o.status', $input['status']];
        }
        if (!empty($input['sync_status'])) {
            $where[] = ['o.sync_status', $input['sync_status']];
        }
        if (!empty($input['shopname'])) {
            $where[] = ['su.shopname', 'like', '%' . trim($input['shopname']) . '%'];
        }
        $list = Order::GetReportBillData($where);
        $data = [];
        if ($list) {
            foreach ($list as $k => $v) {
                $data[] = [
                    'ID'=> $v['id'],
                    'trade_no'=> $v['trade_no'],
                    'create_time'=> dateFormat($v['create_time']),
                    'goods_title'=> $v['goods_title'],
                    'setmeal_title'=> $v['setmeal_title'],
                    'amount'=> $v['amount'],
                    'month_total'=> $v['month_total'],
                    'rent_total'=> $v['rent_total'],
                    'realname'=> $v['realname'],
                    'mobile'=> $v['mobile'],
                    'region'=> $v['prov'] . '-' . $v['city'] . '-' . $v['area'],
                    'address'=> $v['address'],
                    'order_status_name'=> isset(OrderService::ORDER_STATUS_NAME_LIST[$v['status']]) ? OrderService::ORDER_STATUS_NAME_LIST[$v['status']] : '',
                    'sync_status_name'=> isset(OrderService::SYNC_STATUS_NAME_LIST[$v['sync_status']]) ? OrderService::SYNC_STATUS_NAME_LIST[$v['sync_status']] : '',
                    'shopname'=> $v['shopname'],
                ];
            }
        }
        return $data;
    }


    /**
     * 提现审核列表导出
     * @param $input
     * @return array
     */
    public static function withdrawalExamineListExport($request)
    {
        $payeeName = $request->input('payeeName','');
        $payeeAccount = $request->input('payeeAccount','');
        $shopName = $request->input('shopName','');
        $applyTime = $request->input('applyTime','');
        $arrivalTime = $request->input('arrivalTime','');
        $status = $request->input('status','');
        $where = [];
        if($payeeName){
            $where[] = ['w.payee_name','like','%'.$payeeName.'%'];
        }
        if($payeeAccount){
            $where[] = ['w.payee_account','like','%'.$payeeAccount.'%'];
        }
        if($shopName){
            $where[] = ['s.shopname','like','%'.$shopName.'%'];
        }
        if($status){
            $where[] = ['w.status',$status];
        }
        if($applyTime){
            $applyTime = explode('~', $applyTime);
            $where[] = ['w.apply_time','>=',$applyTime[0]];
            $where[] = ['w.apply_time','<=',$applyTime[1]];
        }
        if($arrivalTime){
            $arrivalTime = explode('~', $arrivalTime);
            $where[] = ['w.arrival_time','>=',$arrivalTime[0]];
            $where[] = ['w.arrival_time','<=',$arrivalTime[1]];
        }
        $list = ShopWithdrawal::from('sp_shop_withdrawal as w')
            ->leftjoin('sp_shop_user_2 as s','s.member_id','w.member_id')
            ->select(
                's.shopname'
                ,'w.id','w.member_id','w.withdrawal_amount','w.payee_name','w.payee_account','w.alipay_trade_no','w.out_biz_no','w.out_biz_no'
                ,'w.pay_type','w.status','w.refuse_reason','w.apply_time','w.check_time','w.admin_id','w.arrival_time'
            )
            ->where($where)
            ->orderBy('w.id','desc')
            ->get();
        $list = obToArray($list);
        if(!empty($list)){
            foreach ($list as $k=>$item){
                $list[$k]['admin_name'] = NewAdmin::where(['id'=>$item['admin_id']])->value('username');
            }
        }
        $data = [];
        if ($list) {
            foreach ($list as $k => $v) {
                $data[] = [
                    'ID'=> $v['id'],
                    'account_type'=> '租金账户',
                    'shopname'=> $v['shopname'],
                    'withdrawal_type'=> '支付宝',
                    'withdrawal_amount'=> $v['withdrawal_amount'],
                    'payee_account'=> $v['payee_account'],
                    'payee_name'=> $v['payee_name'],
                    'apply_time'=> $v['apply_time'],
                    'arrival_time'=> $v['arrival_time'],
                    'status'=> ShopWithdrawal::WITHDRAWAL_STATUS_LIST[$v['status']],
                    'refuse_reason'=> $v['refuse_reason'],
                    'check_time'=> $v['check_time'],
                    'admin_name'=> $v['admin_name'],
                ];
            }
        }
        return $data;
    }
    /**
     * 订单列表
     * @param $request
     * @param array $where
     * @return array
     */
    public static function orderXxfList($request, $where = [],$username='',$sale_user_id='')
    {
        $riskName = $request->input('username', '');
        $riskMobile = $request->input('riskMobile', '');
        $orderCreateTime = $request->input('orderCreateTime', '');
        $page = $request->input('page', 1);
        $pageSize = $request->input('pageSize', 10);
        $begin_time=strtotime(date('2025-03-15'));
        $end_time=strtotime('-3 days');
        $where[] = ['o.create_time','>', $begin_time];
        $where[] = ['o.create_time','<', $end_time];
        $where[] = ['o.status',OrderService::ORDER_STATUS_CENCEL];
        $radar = $request->input('radar', 0);
        $order_ids = $request->input('order_ids', '');
        $user_status= $request->input('user_status', '');

        if ($riskName) {
            $where[] = ['m.cert_name', 'like', '%' . $riskName . '%'];
        }

        if ($riskMobile) {
            $where[] = ['m.mobile', $riskMobile];
        }

        if ($orderCreateTime) {
            $orderCreateTime = explode('~', $orderCreateTime);
            $where[] = ['o.create_time', '>=', strtotime($orderCreateTime[0])<$begin_time?$begin_time:strtotime($orderCreateTime[0])];
            $where[] = ['o.create_time', '<=', strtotime($orderCreateTime[1])>$end_time?$end_time:strtotime($orderCreateTime[1])];
        }

        $uid=Order::query()->where('status','>',4)->pluck('uid');
        $uid=obToArray($uid);
        $users = MemberOther::query()->whereBetween(DB::raw('YEAR(CURDATE()) - SUBSTR(cert_no, 7, 4)'), [20, 41])->pluck('id');
        $users=obToArray($users);
        $orderList = Order::from('sp_order_radar_xinyan as xy')
            ->leftjoin('sp_order_2 as o', 'o.id', 'xy.order_id')
            ->leftjoin('sp_member_2 as m', 'o.uid', 'm.id')
            ->groupBy('m.cert_no')
            ->where('xy.member_id', '1693377836')
            ->where('xy.result', '<>', '')
            ->whereNotIn('o.uid', $uid)
            ->whereIn('o.uid', $users)
            ->select(
                'o.id','o.create_time',  'o.username','o.status','o.phone_brand', 'm.mobile', 'm.cert_name', 'm.cert_no',
            )
            ->where($where);

        if($order_ids||$user_status){
            $orderList->whereIn('o.id', $order_ids);
        }

        $orderList->orderBy('o.create_time', 'desc');
        $list = obToArray($orderList->get());


        if (isset($list)) {
            $data [] =[];
            foreach ($list as $k => $value) {
                $data[] = [
                    'create_time' => $value['create_time'],
                    'name' => $value['cert_name'],
                    'mobile' => $value['mobile'],
                    'cert_no' => $value['cert_no'],
                    'age' => getAgeByID($value['cert_no']),
                    'sex' => get_sex($value['cert_no']),
                    'phone_brand' => $value['phone_brand'],
                ];
            }
        }


        return $data;
    }


}


